Search code examples
mysqlsqlsequelpro

SQL UPDATE statement from SELECT statement?

I have an id and abbreviation column in a table. I would like to update the abbreviation column in another database on another server.. I know how to get a SQL select statement, but how would I get an UPDATE statement from this that to run on the other database?

Basically I want to get something like:

UPDATE table SET abbrv=x WHERE id=1;
UPDATE table SET abbrv=y WHERE id=2;

...

How would I do this?

Solution

  • An easy way to do this is create a SELECT statement to generate the UPDATE statements:

    SELECT 
    CONCAT('UPDATE home_provider SET abbrv="', abbrv, '" WHERE id=', id, ';')
FROM home_provider

    This will then give you:

    UPDATE home_provider SET abrv="ACA" WHERE id=1;
UPDATE home_provider SET abrv="ALL" WHERE id=2;
UPDATE home_provider SET abrv="ARK" WHERE id=3;
UPDATE home_provider SET abrv="ART" WHERE id=4;
...