msgbartop
ASP.NET : C#: VB.NET : SQL Server : Oracle : Sharepoint : PHP : SSIS : SSRS : Wordpress : Plugins : Technology News
msgbarbottom

28 Jul 10 How to Update a Single Table with multiple join

In one of my last assignment I had to do some enhancement to the existing stored procedure written in SQL Server 2008. I noticed bunch of lines of sql code written to update a single table inside and using while loop because of a check where key field should exist in other table. I was surprised because all what was written could be easily done with one single SQL statement.

So here is the SQL query to update a single table based on multiple join, I didn’t try this in SQL Server 2005 but it should work in that version too.

UPDATE c
SET c.UpdateMe = t.IamNewValue
FROM TableToUpdate c
JOIN TableToCheck t ON c.MainKey = t.MainKey

In the query above, you can add multiple tables to check same way table TableToCheck is used, also you can add multiple columns to verify certain information before updating like JOIN TableToCheck t ON c.MainKey = t.MainKey and c.SecondKey = t.SecondKey.