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.
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' );