By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,519 Members | 1,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,519 IT Pros & Developers. It's quick & easy.

update with a one to many

P: 5
Hi,

I am trying to do the following

update table A with the latest record from table B.

TABLE A has
PID (primary key)
ID
YEAR

TABLE B is the same as above. There is a one to many relationship from A to B based on the ID.

I ran the following update and it worked but it took the first record for each ID in TABLE B and updated that to TABLE A. Instead, i want to be able to update TABLE A only with the most recent record from B

UPDATE A
SET A.YR = B.YR
FROM A,B
WHERE A.ID=B.ID

I also tried creating a temp table with the information from table B and then ordering that table and then running the update, but that still did not update the latest record?

Thanks
Chris
Jan 30 '08 #1
Share this Question
Share on Google+
3 Replies


deepuv04
Expert 100+
P: 227
Hi,

I am trying to do the following

update table A with the latest record from table B.

TABLE A has
PID (primary key)
ID
YEAR

TABLE B is the same as above. There is a one to many relationship from A to B based on the ID.

I ran the following update and it worked but it took the first record for each ID in TABLE B and updated that to TABLE A. Instead, i want to be able to update TABLE A only with the most recent record from B

UPDATE A
SET A.YR = B.YR
FROM A,B
WHERE A.ID=B.ID

I also tried creating a temp table with the information from table B and then ordering that table and then running the update, but that still did not update the latest record?

Thanks
Chris

i didnt get you exactly what you want....

try the following query probably will help you....( from what i understand from the above )


UPDATE A
SET A.YR = b.yr
FROM A,B
WHERE A.ID=B.ID
and b.id = ( select top 1 id from b order by id desc)


or

UPDATE A
SET A.YR = b.yr
FROM A,B
WHERE A.ID=B.ID
and a.id = ( select top 1 id from b order by id desc)


thanks
Jan 30 '08 #2

debasisdas
Expert 5K+
P: 8,127
In a temporary table insert the new record entry of table B.

Delete the old records from the temp table.

Update table A from temp table .
Jan 31 '08 #3

P: 5
Thank you deepuv04.

That is what i was looking for.
Feb 18 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.