DELETE within SELECT

[Dom]

Hey there,

I would like to execute the following query to perform deletes.
Unfortunately it only returns a list of the delete queries without
actually performing them. How can I get the deletes to be executed? I
am running MySQL 4.0.20-standard.

$query = "SELECT concat('DELETE FROM temp WHERE prodid =
\'',prod.ProdID, '\';') AS '' FROM prod LEFT JOIN catalog ON
prod.ProdID = catalog.ProdID WHERE catalog.ProdID IS NULL";

All help really appreciated!

Thanks,

Dom

[Bill Karwin]

Dom wrote:

Hey there,

I would like to execute the following query to perform deletes.
Unfortunately it only returns a list of the delete queries without
actually performing them. How can I get the deletes to be executed? I
am running MySQL 4.0.20-standard.

$query = "SELECT concat('DELETE FROM temp WHERE prodid =
\'',prod.ProdID, '\';') AS '' FROM prod LEFT JOIN catalog ON
prod.ProdID = catalog.ProdID WHERE catalog.ProdID IS NULL";

Well, you can execute this query and get results back, right? So each
row of the result set becomes a new $query that you can execute.

Another option is to use a multi-table delete, which is a clever (but
non-standard) extension to SQL implemented in MySQL. For example:

DELETE FROM temp
USING temp INNER JOIN prod ON temp.prodid = prod.ProdID
LEFT OUTER JOIN catalog ON prod.ProdID = catalog.ProdID
WHERE catalog.ProdID IS NULL

See http://dev.mysql.com/doc/mysql/en/delete.html for more info on the
multi-table deletes.

Regards,
Bill K.