MySQL Syntax Error on update statement

Question

I have the current code,

UPDATE i
SET LeadInventor = 
    CASE 
        WHEN gs.FirstInventorName IS NULL THEN 'No' 
        ELSE 'Yes' 
    END
FROM patentdb.Inventor i
    LEFT JOIN patentdb.generalsource gs
    ON i.InventorFirst + ' ' + i.InventorLast = gs.FirstInventorName

and I dont understand why it throws the following error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM patentdb.Inventor i     LEFT JOIN patentdb.generalsource gs     ON i.Invent' at line 7

The FROM statement is what MYSQL is underlining

Justin ᚅᚔᚈᚄᚒᚔ · Accepted Answer · 2012-12-05 20:11:02Z

1

This is because the FROM clause isn't valid in a MySQL UPDATE statement:

http://dev.mysql.com/doc/refman/5.6/en/update.html

You may have better luck with this:

UPDATE Inventor i, generalsource gs
SET i.LeadInventor = CASE
  WHEN gs.FirstInventorName IS NULL THEN 'No'
  ELSE 'Yes'
END
WHERE i.InventorFirst + ' ' + i.InventorLast = gs.FirstInventorName

edited Dec 5, 2012 at 20:11

answered Dec 5, 2012 at 19:54

Justin ᚅᚔᚈᚄᚒᚔ

15.4k8 gold badges55 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

amk · Accepted Answer · 2012-12-05 20:34:36Z

0

The syntax is incorrect. Have a look at this query:

UPDATE patentdb.Inventor i
  LEFT JOIN patentdb.generalsource gs
  ON CONCAT(i.InventorFirst, ' ', i.InventorLast) = gs.FirstInventorName
SET LeadInventor = 
  CASE WHEN gs.FirstInventorName IS NULL THEN 'No' ELSE 'Yes' END

In addition, I want to say that in MySQL it is better to use CONCAT function to make new string.

answered Dec 5, 2012 at 20:34

amk

2821 silver badge3 bronze badges

Collectives™ on Stack Overflow

MySQL Syntax Error on update statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related