Re: nested dbi queries question

[VSarkiss]

This really has nothing to do with Perl or DBI. You can do it with a single SQL, barring minor SQL dialect variations (you don't mention what database you're using).

SELECT *
INTO sp_err
FROM sp
WHERE NOT EXISTS (
    SELECT 1
    FROM supplier
    WHERE sp.num = supplier.num
)

If you have to execute that from Perl, just put the whole thing in a $dbh->do(...).

Update
The SELECT * INTO form is non-standard SQL that's supported only a few places (Sybase comes to mind). The equivalent in standard SQL, presuming sp and sp_err have the same columns, would be:

INSERT INTO sp_err
SELECT * FROM sp
WHERE NOT EXISTS (
    SELECT 1
    FROM supplier
    WHERE sp.num = supplier.num
)

As to "moving" rows: your original post didn't do that: it only inserted rows into sp_err. As to why it didn't work, see my reply elsewhere in this thread.

[philosophia]

that sql didn't move the rows i also tried $sql = "SELECT * INTO sp_err FROM sp WHERE NOT EXISTS ( SELECT * FROM sp, supplier WHERE sp.snum = supplier.snum)";

[runrig]

VSarkiss's sql statement without the 'INTO sp_err' clause should be selecting the rows you want. It's up to you to tweak it so that it is selecting those rows into the sp_err table. That depends on the database (which you haven't told us what it is). Maybe it's not exactly 'INTO sp_err', maybe it's 'INSERT INTO sp_err (select statement here)' (I'm betting on that). The select statement itself, though, should be correct.

[philosophia]

thanks. your queries were correct. i found out i have to upgrade mysql in order to use subqueries