CODEWITHSUNDEEP
mysqlsqlsql-update
Peter Craig
Peter Craig

Reputation: 7289

Updating one mysql table based on calculations using variables in another table

I'm trying to update one table based on values in another table. What's wrong with the following request? Error: Unknown column 'source.col3' in 'where clause'

UPDATE target 
  SET target.col1 = source.col1 * target.col2,
WHERE target.col3 = source.col3

Upvotes: 1

Views: 6115

Answers (1)

ChssPly76
ChssPly76

Reputation: 100706

Well, for one you're not specifying 'source' as a table anywhere.

MySQL actually supports multiple table update, so you could write your code as:

UPDATE target, source
   SET target.col1=source.col1*target.col2
 WHERE target.col3=source.col3

Now whether that would actually do what you want I can't tell without knowing more about your tables.

Upvotes: 8

Related Questions