MySQL update from not working - syntax error 1064

Question

I have a table that contains an oldid and an oldparid. I want to update the rows where row.oldpar = otherrow.oldid but I'm having a problem with my query.

UPDATE 01_menu_main SET parid = x.newpar
FROM (SELECT oldid, id as newpar FROM 01_menu_main) x
WHERE x.oldid = 01_menu_main.oldpar

The error I'm receiving is this

Is there something I'm missing? Thanks!

You did miss something; `UPDATE ... FROM` is not valid syntax. What exactly are you trying to accomplish? — Darwin von Corax, Jan 25 '16 at 18:09
From everything I've read, it's possible to do. I found this answer here and was trying to create a variation of it....although I suppose I'm basing that on everything on the internet being true...lol http://stackoverflow.com/questions/13881649/update-statement-using-nested-query — b3tac0d3, Jan 25 '16 at 18:11
It's a non-standard extension, which some DBMSs implement and some (like MySQL) don't. — Darwin von Corax, Jan 25 '16 at 18:14
Ahhhhhhhh - well that makes much more sense. I'm likely looking at a join then. So much for the easy way out lol — b3tac0d3, Jan 25 '16 at 18:15

Sibidharan · Answer 1 · 2016-01-25T18:18:28.160

1

You had an syntax error. The query should be

UPDATE table SET key=value WHERE condition

Change the query according to above syntax and let me know.

UPDATE

UPDATE 
    01_menu_main t1
      JOIN
    01_menu_main t2 
      ON 
    t2.oldid = t1.oldpar 
SET
    t1.parid = t2.newpar

Check the above code works? You have to use JOIN for what you are trying.

edited Jan 25 '16 at 18:18

answered Jan 25 '16 at 18:09

Sibidharan

2,717
2
26
54

I'm sorry but I don't follow - in my mind I have the key an value. Could you explain a little bit more in depth please. – b3tac0d3 Jan 25 '16 at 18:12

score 1 · Accepted Answer · answered Jan 25 '16 at 18:14

1

Use join for it

update 
    01_menu_main t1
    join 01_menu_main t2 on t2.oldid = t1.oldpar
set
    t1.parid = t2.newpar

answered Jan 25 '16 at 18:14

Max P.

5,579
2
13
32

This was 100% what I needed. It didn't occur to me that MySQL didn't allow the easy version of this. Thank you for your help! – b3tac0d3 Jan 25 '16 at 18:18

MySQL update from not working - syntax error 1064

2 Answers2