Error #1093

Discussion in 'MySQL' started by clix99, Jan 19, 2009.

0
  1. #1
    Hi,
    I'm using a MySql database and phpMyAdmin.

    I need to update a field for the first 10 rows of a query results (and not for all the results)

    I'm trying to do this:

    UPDATE 'Users1' SET Age = 0 WHERE ID = (SELET ID FROM 'Users1' LIMIT 0, 10)

    But get this error:

    #1093 - You can't specify target table 'Users1' for update in FROM clause

    Any idea how i can do this?
    Thanks
     
    clix99, Jan 19, 2009 IP
  2. gnp

    gnp Peon

    Messages:
    137
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You can limit directly in the UPDATE statement..

    
UPDATE 'Users1' 
SET age=0
ORDER BY id
LIMIT 0,10
    Code (markup):
    reference: http://dev.mysql.com/doc/refman/5.0/en/update.html
     
    gnp, Jan 19, 2009 IP
  3. clix99

    clix99 Well-Known Member

    Messages:
    133
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    116
    #3
    That's was my first try actually,

    UPDATE 'Users1' SET Age=0 LIMIT 0 ,10


    I get this error where i try it:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10' at line 1

    Any Other Ideas...?
     
    clix99, Jan 19, 2009 IP
  4. gnp

    gnp Peon

    Messages:
    137
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Seems that limit in UPDATE does not take two arguments, just one (the number of rows..)

    so 
    
UPDATE 'Users1' 
SET age=0
ORDER BY id
LIMIT 10
    Code (markup):
     
    gnp, Jan 19, 2009 IP
    Smyrl likes this.
  5. crivion

    crivion Notable Member

    Messages:
    1,669
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    210
    Digital Goods:
    3
    #5
    UPDATE `Users1` SET `Age` = '0' LIMIT 0 ,10
     
    crivion, Jan 19, 2009 IP