where in clause in nested query

Question

I am using mysql and php. I am unable to delete any records even if records exist to meet the condition specified in the query , please help to identify the problem, my query is

Table1 (id, LName) table2(id,LName)

delete from table1 where Lname in (select Lname from table2);

This is not a good style to ask questions in SO. Please show your table structure and what you have done so far. — Choudhury Saadmaan Mahmid, Mar 25 '14 at 06:40
I have two tables table1(id, name ) and table2(id, name), i want to delete records from table1 if it same name exist in table2. — Ajeet, Mar 25 '14 at 06:44
Please add the table description and other details in your comment itself. — Neels, Mar 25 '14 at 06:49
Are you getting any error or just a `0 rows modified` warning. — Neels, Mar 25 '14 at 06:50
Is Lname a typo? Your tables seem to have a column called name — shree.pat18, Mar 25 '14 at 06:52

score 2 · Answer 1 · answered Mar 25 '14 at 07:11

2

delete from table1 where Lname in (select Lname from table2);

Its Work for me. Maybe it have case problem so try like this

delete from table1 where upper(Lname) in (select upper(Lname) from table2);

answered Mar 25 '14 at 07:11

Sathish

4,419
4
30
59

score 0 · Answer 2 · answered Mar 25 '14 at 07:02

According to the table structure you provided in your comment, the query should be somewhat like this:

delete from table1
where name in 
(select name 
 from table2)

But according to your question, it seems your table don't seem to have a column named 'Lname'.

If your column name is actually 'Lname', then the query can be re-written as:

delete from table1
where Lname in 
(select Lname 
 from table2)

Please, please be extra careful mentioning table structure, you MUST post the appropriate name of your tables and columns, and with correct spelling.

score 0 · Answer 3 · answered Mar 25 '14 at 09:59

0

Another option is using a JOIN:-

DELETE table1
FROM table1 
INNER JOIN table2
ON table1.Lname table2.Lname

but your original SQL should work

answered Mar 25 '14 at 09:59

Kickstart

21,403
2
21
33

where in clause in nested query

3 Answers3