On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, November 2, 2008

Update a Table Based on Data from Another Table

This first update is supplying data to TableA from TableB if either field is missing.
Update TableA a
Set (
a.Field1
, a.Field2
) = (
Select b.Field1
, b.Field2
From TableB b
Where b.Key = a.Key
)
Where Field1 Is Null
Or Field2 Is Null;
The second example updates TableA with the data from TableB. The 'Where Exists' is necessary if TableA has records with keys that does not find a match on TableB. Without it, the data in TableA would be set to null when a key can't be found in TableB.
Update TableA a
Set (
a.Field1
, a.Field2
) = (
Select b.Field1
, b.Field2
From TableB b
Where b.Key = a.Key
)
Where Exists (
Select 1
From TableB b
Where b.Key = a.Key
);
For examples and more details, see an article written by Duke Ganote at http://tinyurl.com/2vphxg.

No comments:

Post a Comment