Update mysql table where select from same table

Question

I am new to MYSQL query statements and i am currently stuck at some piece of code as following

UPDATE fb_messages
SET sent = 'Yes' 
WHERE msg_id = (SELECT MAX(msg_id) AS MSG_ID 
                FROM fb_messages
                WHERE sent = 'No')

I am getting this error

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

I searched for a solution to work around for similar cases but i really couldn't find, please help

@Amandeep Jiddewar It seems so but unfortunately when i put it in my SQL section and hit Go the above error appears to me — Amro, Mar 10 '13 at 18:53

score 7 · Answer 1 · edited May 23 '17 at 11:48

This should work.

UPDATE fb_messages fb1, (SELECT MAX(msg_id) AS MSG_ID 
                FROM fb_messages
                WHERE sent = 'No') fb2
SET sent = 'Yes' 
WHERE fb1.msg_id = fb2.MSG_ID

Note : This will create a temporary, and if there are large number of rows then query gets slow, try to create an temporary table with index/primary key Link (Source : Alex Comment)

see this fiddle

echo_Me · Accepted Answer · 2013-03-10T19:29:48.717

2

try this

   UPDATE fb_messages
   SET sent = 'Yes'
   WHERE sent = 'No'
   ORDER BY msg_id DESC
   limit 1

edited Mar 10 '13 at 19:29

answered Mar 10 '13 at 19:07

echo_Me

37,078
5
58
78

Thank you @echo_me your second option worked for me like a charm – Amro Mar 10 '13 at 19:25
@user2151383 Nice that it helped you!. make sure to accept the answer as solved.good luck – echo_Me Mar 10 '13 at 19:29

score 0 · Answer 3 · edited May 23 '17 at 12:00

"In MySQL, you can't modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html"

Break it up into two statements.

CREATE TABLE Updates (
  msg_id int
);

INSERT Updates (msg_id)
SELECT
  MAX(msg_id)
FROM fb_messages
WHERE sent = 'No';

UPDATE fb_messages o, Updates u
SET o.sent = 'Yes'
WHERE o.msg_id = u.msg_id

There's a workaround covered in the article below

Reference: MySQL Error 1093 - Can't specify target table for update in FROM clause

score -1 · Answer 4 · edited Nov 04 '13 at 08:49

-1

Please add one more select query in between WHERE condition

Example:

UPDATE t1 
SET t1.Status = 5
WHERE t1.SearchID NOT IN 
(SELECT TMP.SearchID FROM 
    (SELECT MIN(t1.SearchID) AS SearchID
  FROM t1 
 WHERE t1.ID = '750') TMP)
 AND t1.ID = '750'

edited Nov 04 '13 at 08:49

HRM

2,097
6
23
37

answered Nov 04 '13 at 08:27

shailesh savani

1

Update mysql table where select from same table

4 Answers4