I have two databases with identical table structures

Dest.db = main Source.db = src

the table is called


structure of colorramp is

ID | name | xml | favorite


main.colorramp needs to be updated with items from src.colorramp WHERE name=name. (So I don't want to use the ID or PRIMARYKEY)

I have an sql query that looks like the following.

UPDATE colorramp 
SET colorramp.xml=(SELECT s1.xml FROM src.colorramp s1 WHERE colorramp.name=s1.name),
SET colorramp.favorite=(SELECT s1.favorite FROM src.colorramp s1 WHERE s1.name=colorramp.name)

But i keep getting the error below.

[Error: near ".":syntax error]

Help much appreciated.

  • 1
    Have you tried looking at the UPDATE syntax in the manual? – mustaccio Mar 21 '18 at 14:54

The problem is that you are using table names (SET colorramp.xml=...). This is not allowed; just use SET xml=... instead.

Anyway, the entire statement can be simplified:

UPDATE main.colorramp
SET (xml, favorite) = (SELECT xml, favorite
                       FROM src.colorramp
                       WHERE main.colorramp.name = src.colorramp.name);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.