Copying Data - Conditionally

[Anon]

Is there a way to use INSERT ... SELECT in mysql to copy data from tableA.field2 to tableB.field2 if the data is not in tableB.field2 already? example:

INSERT INTO tableB (field2) SELECT field2 FROM tableA WHERE IS NOT IN tableB (field2)

IS NOT IN isn't valid syntax I'm sure... but is there something that would work?

Thanks in advance!

Mark

PS yes, I know it could be done via a longer php comparison, I'm hoping to avoid it as I have A LOT of these queries to make

[Anon]

Normally this should be done by using a subselect, but since this is not supported by Mysql, you could try the following

INSERT INTO tableB (field2)
SELECT field2
FROM tableA ta left outer join tableB tb
on ta.field2 = tb.field2
where tb.field2 is null

I do not know if this works, Mysql may choke on tableB being present in the select statement.

[Anon]

Actually "IS NOT IN" is very close to valid syntax.

What you want to do isn't going to be fast, but this should do it for you.

INSERT INTO tableB (field2)
SELECT field2 FROM tableA
WHERE field2 NOT IN (SELECT field2 FROM table😎;

Basically you will be scanning tableB for every row in tableA and deciding if you want to insert that row into tableB. This might not re-scan tableB after each insert so if you have duplicates in tableA you'd better throw a DISTINCT in the SELECT statement.

[Anon]

lol

You answered it in your second part actually... and I feel like an idiot 🙂

INSERT INTO tableB (field2) SELECT DESTINCT field2 FROM tableA

thanks 🙂

Mark