CODEWITHSUNDEEP
sqlsql-serversql-delete
Werner
Werner

Reputation: 81

sql delete result from query

I have two tables, same structure

I want to delete row 2 in the clients table

 SELECT * 
 FROM clients 
 WHERE EXISTS (SELECT * FROM clients3 WHERE clients3.id = clients.id))

gives me the row 2. But I do not know how to delete.

DELETE * 
FROM clients 
WHERE (SELECT * 
       FROM clients 
       WHERE EXISTS (SELECT * FROM clients3 WHERE clients3.id = clients.id))

does not work.

Upvotes: 0

Views: 9982

Answers (3)

Eid Morsy
Eid Morsy

Reputation: 966

Try this

   Delete from clients where id  in(select clients3.id from clients3)

Upvotes: 0

Parag Meshram
Parag Meshram

Reputation: 8511

No need to refer clients table twice in your query and remove * from DELETE clause -

DELETE c1
FROM clients c1
INNER JOIN clients3 c3 ON c3.id = c1.id --INNER JOIN will work as EXISTS for 1-1 Relation

Upvotes: 2

John Woo
John Woo

Reputation: 263693

You need to create temporary table for the subquery,

DELETE 
FROM clients 
WHERE ID IN 
(
    SELECT ID
    FROM
    (
        SELECT ID FROM clients WHERE EXISTS (SELECT * FROM clients3 WHERE clients3.id = clients.id)
    ) x
)

Upvotes: 2

Related Questions