MJH MJH - 5 months ago 12
SQL Question

update with inner join raising a syntax error

I have the following tables

enter image description here

and I want to update table Contacts, so that the field companyId will be newCompanyId in table2.

I've tried the following query:

UPDATE contacts
SET contacts.companyId = table2.newComapnyId
FROM contacts
INNER JOIN table2
ON contacts.contactId = table2.conatctId


But I get a syntax error. I've searched, and as far as I could find this is how I'm supposed to write the query.

(Please forgive the typos, in the real query I double checked and there are no misspellings of names and such...)

Answer

In most RDMS you don't have to specify the FROM-clause as it's already included in the statement. Try this:

UPDATE contacts c
INNER JOIN table2 t
ON c.contactId = t.conatctId
SET c.companyId = t.newComapnyId