MySQL IN Statement with Subquery

Databases
1

Hello,

empno | dep_id | sec_id | pos_id

38084 | 5 | 3 | 1
38084 | 5 | 3 | 1
38084 | 5 | 3 | 1

Now i want to delete only one record of this, so i found LIMIT does not work with DELETE.
So i tried like this :


$query="DELETE FROM employees 
        WHERE empno
		     IN 
                    (SELECT empno 
		     FROM employees
		     WHERE empno='$empno'
		     AND dep_id='$dep_id'
		     AND sec_id='$sec_id'
		     AND pos_id='$pos_id'
                     LIMIT 1
		     )	";
[COLOR="#FF0000"]ERROR : You can't specify target table 'employees' for update in FROM clause[/COLOR]

:rolleyes:

2

Give the employee table in the subquery an alias.
But it won’t work anyway, it’ll delete all rows with that id.

3

Hai guido

$query="DELETE FROM employees 
        WHERE empno
		IN (SELECT empno 
		    FROM employees as [COLOR="#006400"]tbl[/COLOR]
			WHERE empno='$empno'
			AND dep_id='$dep_id'
			AND sec_id='$sec_id'
			AND pos_id='$pos_id'
			LIMIT 1
			)
		";

Now it says “This version of MySQL doesn’t yet support 'LIMIT & IN/ALL/ANY/SOME subquery”
so this mean the query is working, but my wamp sql version out date i think …

4

is empno not the PK of the employees table?

if so, you can simplify the sql statement considerably…

DELETE 
  FROM employees 
 WHERE empno='$empno'

if empno is ~not~ the PK of the employees table, you gots other problems

5

oh madness,
i made a mistake in my post number 1, in that it is showing sec_id and pos_id are same for 3 records. but it’s not.
those columns values have diffrent values as showong below. also there is an auto increment column call ‘id’

id | empno | dep_id | sec_id | pos_id

1 | 38084 | 5 | 2 | 1
2 | 38084 | 5 | 7 | 1
3 | 38084 | 5 | 3 | 1

6

sorry folks,
i think i totally messed up.
i really forgot that i have a unique ID column :smiley:
after r937’s question only it came to my mind :smiley:
problem solve when deleted using the combination of ID + Empno.