IN clause for select query

[Craig Stadler]

Can someone help with query syntax regarding IN/EXISTS..

I'm trying to do this:

insert into table2 (field1) select field1 from table1 where field1 not in
(select field1 from table2)
delete from table1 where field1 in (select field1 from table2)

1. Insert field1 from table1 into table2 if it doesn't already exist
there...
2. Once moved, delete from table1.

Any help would be greatly appreciated.

Thanks,
Craig

[Bill Karwin]

Craig Stadler wrote:

Can someone help with query syntax regarding IN/EXISTS..

I'm trying to do this:

insert into table2 (field1) select field1 from table1 where field1 not in
(select field1 from table2)
delete from table1 where field1 in (select field1 from table2)

1. Insert field1 from table1 into table2 if it doesn't already exist
there...
2. Once moved, delete from table1.

Your syntax looks okay, but FWIW subqueries are not implemented in MySQL
4.0 and earlier. So unless you're using MySQL 4.1, subqueries won't work.

Also, notice that the set of field1 from table2 is different after you
insert a bunch of new rows to table2. The subquery in the second step
will return all rows in table2, not just those that you copied in the
first step.

Perhaps you should do this in multiple steps:
1. select field2 from table2; concatenate the result set into a string
of comma-separated values (and quote-delimited if the values are
non-numeric).
2. Run the insert... select from table1 where field1 IN ($result_string)
3. Run the delete from table1 where field1 IN ($result_string)

This technique preserves the set of values for the two operations you
need to do, and it also avoids the possibly-not-supported subquery syntax.

Regards,
Bill K.