MySQL - Insert into a Table from another Table - with a condition


insert into tbl1 (value1,value2)
select value3, value4 from tbl2;

The above code inserts value3 and value4 from tbl2 INTO value1 & value2 of tbl1.

What I would like to do is add a “WHEN” statement, that first compares the two tables to make sure the combination of those two values do not exist before adding them.
EG: when tbl1.value1 & tbl1.value2 != tbl2.value3 & tbl2.value4

How would I properly write the “when” statement and incorporate it into my sample above?

(NOTE: I have added a UNIQUE property to the database so both value1 and value2 of tbl1 must be unique, but that will throw a duplicate error if the SQL is run, and nothing new gets entered if it appears later than the error)

the UNIQUE index was the correct strategy

now, change your query to this –

INSERT [COLOR="#FF0000"]IGNORE[/COLOR]
  INTO tbl1 
     ( value1, value2 )
SELECT value3, value4 
  FROM tbl2

What is the difference between 1st one and second one. I think second one will be more useful as compared to 1st. Because you need not have to manually remove duplicate from table and time also will be reduced.

you can use the second query after condition means after where keywords.