SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MySQL IN Statement with Subquery

    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 :

    Code:
    $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
    		     )	";
    ERROR : You can't specify target table 'employees' for update in FROM clause

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Give the employee table in the subquery an alias.
    But it won't work anyway, it'll delete all rows with that id.

  3. #3
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Give the employee table in the subquery an alias.
    But it won't work anyway, it'll delete all rows with that id.
    Hai guido

    Code:
    $query="DELETE FROM employees 
            WHERE empno
    		IN (SELECT empno 
    		    FROM employees as tbl
    			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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    is empno not the PK of the employees table?

    if so, you can simplify the sql statement considerably...
    Code:
    DELETE 
      FROM employees 
     WHERE empno='$empno'
    if empno is ~not~ the PK of the employees table, you gots other problems
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    is empno not the PK of the employees table?

    if so, you can simplify the sql statement considerably...
    Code:
    DELETE 
      FROM employees 
     WHERE empno='$empno'
    if empno is ~not~ the PK of the employees table, you gots other problems
    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. #6
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    960
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sorry folks,
    i think i totally messed up.
    i really forgot that i have a unique ID column
    after r937's question only it came to my mind
    problem solve when deleted using the combination of ID + Empno.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •