Update table based on value inside this table

Question

I'm trying to update a tablecolumn based on another value inside this table but I keep getting an error.

My query is as follows:

    UPDATE partcreditor 
    SET partcreditor.creditorid = creditor.creditorid
    FROM partcreditor
    INNER JOIN creditor ON partcreditor.creditornr = creditor.creditornr
    WHERE creditor.relgroupid = 1
    AND creditor.creditortypeid = 1

Remove FROM clause and move JOIN closer to UPDATE. Look here: http://stackoverflow.com/a/15209489/4099089 — michaJlS, May 20 '16 at 10:18

score 2 · Accepted Answer · answered May 20 '16 at 10:22

UPDATE partcreditor AS PC
INNER JOIN creditor AS CR ON PC.creditornr = CR.creditornr
SET PC.creditorid = CR.creditorid
WHERE CR.relgroupid = 1 AND CR.creditortypeid = 1

No need to use the FROM clause in the update. As well as use alias name for better readability.

Update table based on value inside this table

1 Answers1