469,622 Members | 2,182 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

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
Jul 23 '05 #1
1 8348
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.
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Rotsj | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
6 posts views Thread by Mark Reed | last post: by
5 posts views Thread by PaulMac | last post: by
6 posts views Thread by Bernd Smits | last post: by
2 posts views Thread by Michael | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.