update with a one to many

[flumonion]

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

[deepuv04]

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

[debasisdas]

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 .

[flumonion]

Thank you deepuv04.

That is what i was looking for.