Keanu
Keanu

Reputation: 53

MySQL: Updating data from one column to another using two matching columns

I'm trying to update columns based on two matching columns: firms.id = investors.firm and investors.firm_role = "ceo", but the server keeps rejecting my syntax. I have tried inner joins, without inner joins, it's all really too complex for me, so I'm not sure what to do next.

UPDATE firms 
JOIN investors USING (firms.id = investors.firm) 
SET firms.ceo = investors.name
WHERE investors.firm_role = "ceo"

Error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.id = investors.firm) SET firms.ceo = investors.name WHERE investors.firm_role =' at line 1

Upvotes: 1

Views: 45

Answers (3)

Feroz
Feroz

Reputation: 153

You may try this

UPDATE firms INNER JOIN investors ON firms.id = investors.firm AND investors.firm_role = "ceo" SET firms.ceo = investors.name

Upvotes: 0

Try this Syntax

update patients set column_name="value" where (condition)

for example im using c# and it goes

update patients set " + dgv.Columns[c].HeaderText + "='" + dgv.Rows[dgv.CurrentCell.RowIndex].Cells[c].Value.ToString() + "' where idno='" + dgv.Rows[dgv.CurrentCell.RowIndex].Cells[0].Value.ToString() + "'"

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

You are very close with your syntax:

UPDATE firms 
LEFT JOIN investors ON firms.id = investors.firm
SET firms.ceo = investors.name 
WHERE investors.firm_role = "ceo"

Refer to this.

Upvotes: 1

Related Questions