HOWTO: MySQL - Update Data in one table from another table

I have just been refactoring a legacy database and needed to update some data in one table from another table in MySQL.

In this case the 2 tables had the same values for the identity colomn.

Syntax:

UPDATE tablename1 t1
SET colomn1 = (SELECT colomn1 from tablename2 t2 WHERE t1.id = t2.id);

For example

If i had tables: ‘products’ and ‘merchandise’ and needed to copy the product title into the merchandise title colomn, I would simply:

UPDATE merchandise m
SET title = (SELECT title from products p where m.id = p.id);

Tags: ,

This entry was posted on Tuesday, November 18th, 2008 at 12:13 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Responses

There have been 3 responses to HOWTO: MySQL - Update Data in one table from another table.

  1. Comment made by Defirso on
    June 5th, 2009 at 4:42 am

    This is interesting. However, I tried to use join between two tables to update. But one of the table having large number of records (more than 1 mio). It seem takes a whole life for the statement to execute. Should the statement you provided take shorter time execution? Haven’t tried yet though.

  2. Comment made by David Stillwell on
    June 10th, 2009 at 2:10 am

    Thanks, this was great!

  3. Comment made by mhasan on
    September 12th, 2009 at 7:13 pm

    Great! It’s works perfect for me. Thanks a lot.

Leave a Reply

Commenting rules

  • Try to keep your comments as relevant as possible.
  • No HTML/JavaScript/BBcode.
  • Don't be abusive: No racism, homophobia or any other nastiness.
  • Feel free to express your opinion, but do so in an eloquent way.

If you do not respect these rules your comments may be edited or even deleted.