Tuesday, October 24, 2006

UPDATE using inner SELECT

Using an UPDATE with an inner joined results set.
Demonstrated for simplicity here, but useful in the update criteria become complicated >
UPDATE person
SET surname = updated.surname
FROM person
INNER JOIN
( SELECT correctdata.pk, correctdata.title, correctdata.surname, incorrectdata.title, incorrectdata.surname
FROM person_import correctdata
INNER JOIN person incorrectdata
ON correctdata.pk = incorrectdata.pk
WHERE incorrectdata.surname is null
) AS updated
ON person.pk = updated.pk

No comments: