Delete multiple rows in sql

Question

I have two tables like this:

Table L   
 A | B  
 C | D

Table K     
 A | B   
 C | D  
 E | F
etc etc

I whish to delete in table K all the lines that exist in Table L.

However when I do delete it will only eliminate the first line. I would like that if more lines where inserted into Table L that coincide with lines in Table K the delete would work as well, this is, not having to do a "manual" delete

Thanks in advance

mysql or sqlite? remove the wrong tag

ScaisEdge
– ScaisEdge

2016-10-01 18:31:43 +00:00
Commented Oct 1, 2016 at 18:31 — ScaisEdge
– ScaisEdge, Commented Oct 1, 2016 at 18:31

Sergey Kalinichenko · Accepted Answer · 2016-10-01 18:46:24Z

1

You can use delete with an alias and an existential quantifier to accomplish that:

delete k
from K as k
where exists (
    select * from L as l
    where l.col1=k.col1 and l.col2=k.col2
)

edited Oct 1, 2016 at 18:46

answered Oct 1, 2016 at 18:40

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sergey Kalinichenko Over a year ago

@SOaddict Right. Thank you!

André Santos Over a year ago

Thanks, my query was mostly right except I had an = after the where clause. that helped!

Teja · Accepted Answer · 2016-10-01 20:47:01Z

0

DELETE  FROM K 
WHERE ( Col1,Col2 ) 
  NOT IN 
( 
  SELECT k1.Col1, k1.Col2
    FROM K k1
    LEFT OUTER JOIN L l1
      ON k1.Col1 = l1.Col1
     AND k1.Col2 = l1.Col2
    WHERE l1.Col1 IS NULL OR l1.Col2 IS NULL
);

edited Oct 1, 2016 at 20:47

answered Oct 1, 2016 at 18:38

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Collectives™ on Stack Overflow

Delete multiple rows in sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related