|
<< Click to Display Table of Contents >> UPDATE from foreign columns |
  
|
|
<< Click to Display Table of Contents >> UPDATE from foreign columns |
|
Updating from foreign tables and with conditions including more than one column is not described in the original manual.
However this is all available:
TableA |
TableB |
The updated TableA |
Consider having the table TableA, where you want to update with the corresponding ValB-values from TableB on records matching on both the Item-ID and on the ItemDate.
Also you want to keep the Val-values of records having no foreign key match and to prevent these field to be replaced with NULL-values, that may result with some other methods.
With two column matches the statement should look for example like this:
UPDATE TableA
SET Val =
(SELECT TableB.ValB
FROM TableB
WHERE ItemB = TableA.Item
AND ItemBDate = TableA.ItemDate
)
/* Prevents NULL'ing fields for non-matching records: */
WHERE EXISTS
(SELECT ItemB
FROM TableB
WHERE TableA.Item = TableB.ItemB
AND TableA.ItemDate = TableB.ItemBDate
)
;
or like:
UPDATE TableA
SET Val =
(SELECT TableB.ValB
FROM TableB
WHERE ItemB = TableA.Item
AND ItemBDate = TableA.ItemDate
)
/* Prevents NULL'ing fields for non-matching records: */
WHERE Item IN
(SELECT ItemB
FROM TableB
WHERE TableA.Item = TableB.ItemB
AND TableA.ItemDate = TableB.ItemBDate
)
;
With this you will get the result at the far right sparing the fourth row having no match on ItemDate in the TableB.
Supplementary notes on LocalSQL added by Niels Knabe
