May 9, 2007 at 2:41 pm
I have 3 tables
A -table needing updating
B - Transaction
C- History
Tables B & C have the exactly same structure.
I need to update table A from either B or C depending on which table has the acctno that is found in table A
B nor C will ever have the same acctno in the table at the same time.
Any suggestions
May 9, 2007 at 2:44 pm
You can do a union all between b and c and use that data to do the update.
Or you can simply do 2 different updates.
May 10, 2007 at 9:55 am
How would you use a union in an update. I have tried but is receiving a error
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ')'.
Code
UPDATE
A
SET
Col1 = B.Col1,
col2
= B.Col2,
Col3
= B.Col3,
Col4
= B.col4
FROM
Table1 A WHERE
(
SELECT Col1, Col2, Col3, Col4 FROM Table2 B WHERE B.Col5 = A.Col5
UNION ALL
SELECT Col1, Col2, Col3, Col4 FROM Table3 B WHERE B.Col5 = A.Col5)
May 10, 2007 at 1:22 pm
Here's the correct syntax to do the update.
UPDATE A
SET Col1 = B.Col1,
col2 = B.Col2,
Col3 = B.Col3,
Col4 = B.col4
FROM Table1 A inner join
(SELECT Col1, Col2, Col3, Col4, id FROM Table2
UNION ALL
SELECT Col1, Col2, Col3, Col4, id FROM Table3) dtData
on a.id = dtData.id
May 10, 2007 at 2:12 pm
Thank you so much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply