February 3, 2011 at 12:28 pm
I have two tables
A and X
Table A
1ID 1Name X_diff_ID 1active
1 B C2 yes
2 F G2 yes
Table X
XID XName X_diff_ID 1ID 1active
1 B C2 empty yes
2 F G2 empty yes
I need to update 1ID on X based on X_diff_ID on A, i.e. somehow i have to figure out that c2 (X_DIFF_ID) = 1 on Table A and update 1ID on X with the value of 1
for a small table i could update X based on A with a where but if there is hundrers of values, is there a way to look up table A and update 1ID on table X based on the 1ID and X_Diff_ID on table A i.e two columns
thanks
February 3, 2011 at 1:42 pm
Asuming X_diff_ID in TableA has a unique constraint, you could join the two tables based on X_diff_ID and update TableX.1ID with TableA.1ID.
I don't understand the reference to a small table vs. a few hundred rows. That's still a small table and -assuming proper indexing- the update shouldn't requier any significant amount of time.
As a side note: I'm not sure if your table design is normalized to an acceptable extent. I wouldn't expect X_diff_ID and 1ID in both tables but with different values. You might want to reevaluate the design.
February 3, 2011 at 4:01 pm
A and X
Table A
1ID 1Name X_diff_ID 1active
1 B C2 yes
2 F G2 yes
Table X
XID XName X_diff_ID 1ID 1active
1 B C2 empty yes
2 F G2 empty yes
So i tried this
set X.1ID = A.1ID
from A inner join X
on A.X_diff_ID = X.X_diff_ID
which gives me an error as ambiguos column defined, is there another way of doing this
somehow i have to update X.1D by comparing A.1ID and A.X_diff_ID in table A i.e. the data in table X after update will be like
XID XName X_diff_ID 1ID 1active
1 B C2 1 yes
2 F G2 2 yes
if it was only 10 columns i had to update i would use update with a where clause, but for hunderds of rows i have to compare and then update.
thanks
February 3, 2011 at 4:43 pm
Beside the required changes due to the unusual column names (I had to wrap it in brackets) the following code did work just fine when I tested it:
UPDATE X
SET X.[1ID] = A.[1ID]
FROM A INNER JOIN X
ON A.X_diff_ID = X.X_diff_ID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply