Tuesday, May 5, 2009

How to write inner join in update query T-SQL

When we are very new to t-sql, we generally face a problem like how to make a query to update a table by doing inner join. if you don't know this, your queries become very complex. Below is the example how to write that.

Where it can be useful is,
for example, some how db admin created a table called State, where he is using the StateName as the foreign key instead of the StateID to employee table. Now he wants to update the State column in employee table with the StateID from StateName, we need this inner join in update query.
//Syntax:
Update Table1 set columnName = pt.columnName
from Table1 d
inner join Table2 pt on d.columnName = pt.ColumnName

//Example:
Update Employee set State = s.StateID
from Employee e
inner join State s on e.State = s.StateName 

No comments:

Post a Comment