Tuesday, August 26, 2008

Update from Select with SQLServer

Suppose to be in this situation: we have to update some database records starting from query result. There are 2 solutions: we can manual update datas (it will be a problem with thousands of records) or we can use a T-Sql script to do exatcly what we want. Then only condition is that the starting query must have a field on wich we can make a join on.
The script code is below:

UPDATE T
    SET T.Field1a = Q.Field2a,
    T.Fieldb = Q.Field2b
FROM
    (SELECT FieldID, Field2a, Field2b
    FROM Table WHERE Condition) Q
INNER JOIN TableToUpdate T
    ON T.FieldID = Q.FieldID


Where T is the "Table-To-Update" alias and Q is the Select query alias.
The Join clause do the update exactly like we do an "Update ... Where".

No comments: