How-To Update Tables Using Inner Joins

You've gone through the trouble of creating a select statement that joins a couple of tables together and discovered that some data needs to be updated. How can you do that when you've joined tables?

First, state that you're going to be performing an update...

Code:

UPDATE

Then join the tables that will meet the condition.

Code:

TABLEA AS A INNER JOIN TABLEB AS B ON A.F1=B.F1

Then state what's going to change.

Code:

SET A.X = 1, B.Y = 2

Then we add a WHERE clause.

Code:

WHERE A.Z =10

Tie it all together...

Code:

UPDATE TABLEA AS A INNER JOIN TABLEB AS B ON A.F1=B.F1
  SET A.X = 1, B.Y = 2 WHERE A.Z =10

Problem solved.

Good Luck,

MapMan


Assume nothing, question everything, be explicit not implicit, and you'll always be covered