0

I'm trying to execute this query which finds some elements with duplicate message_id, changes the message_id of all but the last element (largest id)...

UPDATE messages 
SET message_id = $1, parent_id= $2 
WHERE message_id = $2
AND id NOT IN (SELECT MAX(id) FROM messages WHERE message_id= $2

But it keeps giving me this error:

(1093, "You can't specify target table 'messages' for update in FROM clause")

How do I get around this??

Christopher Reid
  • 4,318
  • 3
  • 35
  • 74
  • 1
    possible duplicate of [Mysql error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) – jpw Mar 09 '15 at 21:50
  • Just use another subquery (a small trick for `mysql`) -- `not in (select max(id) from (select id from messages) t ...` – sgeddes Mar 09 '15 at 21:53
  • Seems that it should already be a subquery, OP is missing a closing parenthesis. – EternalHour Mar 10 '15 at 00:16

1 Answers1

0
in this case we can use sub query... see this example..


-- -- 
USE test;

-- -- Create a table
-- create table messages ( id int, message_id int, parent_id int);


-- insert some dummy records
-- insert into messages values(1, 1, 1), (2, 2, 1), (3, 2, 2)

--  ERROR: you can't specify target table messages for update in FROM clause
UPDATE messages
SET message_id = 1, parent_id = 2
WHERE message_id = 2
AND id NOT IN (SELECT MAX(id) FROM messages WHERE message_id = 2);

**-- Success: Possible solution
-- -- wrap the subquery into another sub query**

UPDATE messages
SET message_id = 1, parent_id = 2
WHERE message_id = 2
AND id NOT IN (SELECT subQuery.MaxId FROM (SELECT MAX(id) MaxId FROM messages WHERE message_id = 2) AS subQuery )


-- clean the test db
Raushan Kuamr Jha
  • 463
  • 3
  • 9