CODEWITHSUNDEEP
mysqlsqlselectsql-updatemysql-error-1093
diman4eg
diman4eg

Reputation: 57

MySQL select and update multiple rows from same table

Generally I want to select rows from orders table in my database, that were created after some date, and then update office_id field for all of selected rows to the specific value 12. My MySQL version is 5.5.43. At firs I was trying to put it in one SQL statement like so:

UPDATE `order`
SET office_id = 12
WHERE id IN (  
  SELECT id
  FROM  `order` 
  WHERE created_at >= DATE_FORMAT( '2014-07-02 00:00:00', '%Y.%m.%d 00:00:00' ) 
);

And I was getting error: #1093 - You can't specify target table 'order' for update in FROM clause.

Next I tried to do this with SELECT .. FOR UPDATE statment like so:

START TRANSACTION;

SELECT id
FROM  `order` 
WHERE created_at >= DATE_FORMAT( '2014-07-02 00:00:00', '%Y.%m.%d 00:00:00' ) 
FOR UPDATE;

UPDATE `order`
SET office_id = 12
WHERE id IN ( id );

COMMIT;

Which worked, but if SELECT .. FOR UPDATE statement returns no rows, then office_id = 12 would be applied to all rows in orders table, which I defenetely don't want.

I'm looking for help in modification 1st or 2nd solution for propper working.

Upvotes: 2

Views: 2586

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

If id can be duplicated in order, then you can express the query using a join:

UPDATE `order` o JOIN
       (SELECT distinct o.id
        FROM  `order` o
        WHERE created_at >= DATE_FORMAT( '2014-07-02 00:00:00', '%Y.%m.%d 00:00:00' 
       ) oo
       ON o.id = oo.id
    SET o.office_id = 12;

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

It's much simpler than you're making it. You can apply a WHERE clause directly to an UPDATE statement.

UPDATE `order`
    SET office_id = 12
    WHERE created_at >= DATE_FORMAT( '2014-07-02 00:00:00', '%Y.%m.%d 00:00:00' );

Upvotes: 2

Related Questions