Delete using Union

Question

I want to delete the output of my query:-

SELECT client_id 
FROM   dt.pref 
WHERE  record_create_date < (SELECT current_timestamo - 45 YEARS 
                             FROM   sysibm.sysdummy1) 
UNION 
SELECT client_id 
FROM   ctt.dom 
WHERE  record_create_date < (SELECT current_timestamo - 45 YEARS 
                             FROM   sysibm.sysdummy1)

Please suggest me how to perform delete operation here. Like I want to delete whatever is coming as output. That I need to use joins in my stored procedure

In most SQL compliant systems you can just change the SELECT to a DELETE. You may have to run it twice (once for each side of that UNION). — Robert Harvey, Aug 13 '20 at 12:51
Show me your code. I can't see what you did unless you show me. — Robert Harvey, Aug 13 '20 at 13:10
As u suggested, I did. Like replaced SELECT with DELETE. IT will work for sure if I'll remove union. But I've to use union. — Rio, Aug 13 '20 at 13:21
You still haven't shown me your new code, so now I have to guess. It should be DELETE FROM or DELETE * FROM, not DELETE client_id FROM. — Robert Harvey, Aug 13 '20 at 13:36
Have you removed the UNION? You don't need it. Just execute the two queries separately. — Robert Harvey, Aug 13 '20 at 14:50
No, you do *not* need the UNION. Why would you need the UNION? All the UNION does is combine the results of those two SELECT queries. Just run each DELETE query separately. UNION won't work with DELETE queries. — Robert Harvey, Aug 13 '20 at 17:39
You don't need joins either. Just run each query separately. — Robert Harvey, Aug 14 '20 at 04:00

score 0 · Answer 1 · answered Aug 13 '20 at 14:06

0

Multi-Table delete (deleting from more than one table in the same DELETE) is not standard in SQL, but some platforms supports it.

MySQL does support a multi-table DELETE but its usually used where there is JOIN on the tables. https://dev.mysql.com/doc/refman/8.0/en/delete.html

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

This deletes from t1 and t2

Whether there is a requirement for a join or not, I don't know.

So maybe your SQL would look something like this, but somehow this just doesn't feel right. You'll just have to try it.

DELETE  dt.pref, ctt.dom FROM dt.pref, ctt.dom
WHERE  dt.pref.record_create_date < (SELECT current_timestamo - 45 YEARS FROM sysibm.sysdummy1) 
AND   ctt.dom.record_create_date < (SELECT current_timestamo - 45 YEARS FROM sysibm.sysdummy1)

Probably a better option is to run two separate DELETE statements. If you must have them treated as a single statement, then wrap them in a TRANSACTION.

answered Aug 13 '20 at 14:06

James Stevens

374
2
8

Hi James, how can I wrap it in a TRANSACTION?? – Rio Aug 13 '20 at 14:37
How can I delete using joins? – Rio Aug 13 '20 at 14:47
start with "BEGIN TRANSACTION" and end with "COMMIT" - some SQL servers using some file formats for not support transactions. Personally, I wouldn't bother, unless its incredibly vital. – James Stevens Aug 14 '20 at 10:51
Most SQL platforms DO NOT support deleting using joins, but MySQL does - as per my previous answer - see the `t1, t2` example – James Stevens Aug 14 '20 at 10:54

Delete using Union

1 Answers1