CODEWITHSUNDEEP
mysqlsqlsql-update
Henry Ford
Henry Ford

Reputation: 15

SQL - update records within select statement

Say you are joining two tables but one of the columns you are selecting you want to update with new values, do you have to do that as two separate statements or can you embed an update statement within the select query?

i.e. 

SELECT table1.xxx, table1.yyy, table2.zzz
FROM table1 JOIN
      table2
     ON table1.xxx = table2.zzz
WHERE table1.xxx = 'Y'

UPDATE table1
    SET xxx = 'YES'
    WHERE xxx = 'Y'

Does that make sense or do you need to do the update statement and then do the select query separately?

Upvotes: 1

Views: 255

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

If you need the join for filter the rows you need to update you can use Update with join eg:

  UPDATE table1
  JOIN  table2  ON table1.xxx = table2.zzz
  SET table1.xxx = 'YES'
  WHERE table1.xxx = 'Y'

Upvotes: 3

Related Questions