0

I have the following MySQL update statement that is failing:

update us
set us.name = concat('user_',rpc.userid),us.addr='old_user'
from user us
join (select distinct(h.userid) as userid,max(h.rpc_time) as rpc_time from host h 
join user u on u.id = h.userid group by h.userid order by h.userid) as rpc
on us.id = rpc.userid and rpc.rpc_time < 1070236800;

Gets the following error message:

Error message: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from user us join (select distinct(h.userid) as userid,max(h.rpc_'

I think it might be due to the underscore. Any ideas on how to solve this or get around this?

Andy B
  • 31
  • 3
  • Also check out the MySQL documentation on multi-table update statements. http://dev.mysql.com/doc/refman/5.7/en/update.html – CLAbeel Nov 18 '16 at 18:50
  • always a syntax error when the `SET` comes too soon in the query (like line 2) – Drew Nov 18 '16 at 18:57
  • When I move the SET to elsewhere in the query I am still getting an error message. I moved the SET to the third line, and got the error message: "set us.name = concat('old_user_',rpc.userid),us.emai' at line 2". Then I moved the SET to the end line and again the message: "join (select distinct(h.userid) as userid,max(h.rpc_' at line 2". – Andy B Nov 19 '16 at 17:45

0 Answers0