MySQL update statement

Question

I am trying to update a specific column in a table with data from the column of another table. I tried running the below command, but I am getting a syntax error at line 3.

UPDATE af_application af 
SET application_price=rss.application_price 
FROM rss_applications rss 
WHERE af.application_id=rss.application_id

zerkms · Accepted Answer · 2011-06-29 01:09:42Z

4

UPDATE af_application af, rss_applications rss 
SET application_price=rss.application_price 
WHERE af.application_id=rss.application_id

answered Jun 29, 2011 at 1:09

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zerkms Over a year ago

@David: is application_id covered with any index in rss_applications table? And how many rows are updated?

David Over a year ago

what do you mean by index? Are you referring to whether application_id is the primary key? If so application_id was set to be the primary key of the table. Only ~6000 records are updated.

zerkms Over a year ago

@David: by saying index I mean dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html and dev.mysql.com/doc/refman/5.0/en/create-index.html. If it is already a PK - then yuo have nothing to do anymore. (except of adding af.application_price <> rss.application_price to your where condition.

David Over a year ago

Thanks for the links on indexes.

Francois Deschenes · Accepted Answer · 2011-06-29 02:12:41Z

3

You can't use FROM in an UPDATE statement. You'll want to use JOIN instead.

UPDATE af_application af 
JOIN rss_applications rss 
ON af.application_id=rss.application_id
SET af.application_price=rss.application_price

For more information, see http://dev.mysql.com/doc/refman/5.0/en/update.html and http://dev.mysql.com/doc/refman/5.0/en/join.html.

edited Jun 29, 2011 at 2:12

answered Jun 29, 2011 at 1:09

Francois Deschenes

25.1k4 gold badges67 silver badges61 bronze badges

2 Comments

David Over a year ago

I tried your suggestion and I get a syntax error at line 3 JOIN...

Francois Deschenes Over a year ago

@David - Sorry, I accidentally inverted SET and JOIN! I updated the answer. If it's slow, make sure that you have indexes on the application_id fields in both tables.

Collectives™ on Stack Overflow

MySQL update statement

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related