nested select with delete sql statement

Question

I dunno how do I need to solve it:

i've got a select statement:

SELECT 
       sum_lines.id, 
       sum_lines.line_id, 
       sum_lines.summary_id, 
       sum_lines.text_content, 
       sum_tbl.user_id 
FROM 
       sum_lines 
JOIN 
       sum_tbl 
ON 
       sum_lines.summary_id = sum_tbl.id 
WHERE 
       sum_tbl.user_id = 1

how can I delete everything that this statement is showing with a delete statement?

Age-old problem, with an age-old [answer ... http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins/439768#439768](http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins/439768#439768) — coge.soft, May 08 '13 at 21:23

TheQ · Accepted Answer · 2013-05-08T21:18:58.510

4

If you want to delete from both tables you need two deletes:

delete sum_lines
from sum_lines
join sum_tbl on sum_lines.summary_id = sum_tbl.id where sum_tbl.user_id = 1

delete from sum_tbl 
where sum_tbl.user_id = 1

Just make sure to do a backup before you delete something. It can easily be done with something like this:

select *
into sum_lines_backup
from sum_lines

select *
into sum_tbl_backup
from sum_tbl

edited May 08 '13 at 21:18

answered May 08 '13 at 21:13

TheQ

6,858
4
35
55

My bad, i have updated the answer. You need to specify the table to delete from directly after the "delete" statement. – TheQ May 08 '13 at 21:19

score 0 · Answer 2 · edited May 09 '13 at 07:13

0

Try This:

delete sum_lines from sum_lines as a

join sum_tbl as b on b.id = a.id 

where b.user_id = 1

Make sure the column name sum_lines[id] data must match with sum_tbl[id], otherwise it wont work. Maybe sum_lines.summary_id = sum_tbl.id is not match data on your database.

edited May 09 '13 at 07:13

tessi

13,313
3
38
50

answered May 09 '13 at 06:40

Tan Suiwseng

77
9

nested select with delete sql statement

2 Answers2