SQL update column with correct ID from different table

Question

Okay to start off - I've mucked up!

I have two tables that record all auctions - Bought & Sold - and each Sold record has a bidID value that joins it to the Bought record. Each item that has bought has a unique itemID value but there are occasions where the same item may be bought and sold several times.

Take the following records as an example...

Sold Table:

listingID   itemID           bidID     gamertag           startPrice    binPrice    currentPrice    listingEnds               listingBought
----------- ---------------- --------- ------------------ ------------- ----------- --------------- ------------------------- -----------------------
18661       9011938655       4410      BoundenLeech84     3000          3100        3100            2014-01-31 18:59:20.000   2014-01-31 18:09:32.000
18825       9011938655       4410      BoundenLeech84     2800          2900        2900            2014-01-31 19:53:15.000   2014-01-31 18:54:48.000

Bought Table:

bidID       itemID           gamertag          binPrice   lowestBIN   bidTime
----------- ---------------- ----------------- ---------- ----------- -----------------------
4410        9011938655       BoundenLeech84    2800       3300        2014-01-31 17:59:18
4454        9011938655       BoundenLeech84    2600       3100        2014-01-31 18:53:14

So listing ID 18661 has the bidID 4410 which is correct but the bidID for listingID 18825 is also 4410 which is incorrect as it should be 4454 (as the bidTime of bidID 4454 is after the listingBought time of listingID 18661).

There are hundreds, if not thousands, of records like these so I was wondering if anyone knew if there was a way of writing a script that would save me manually having to do it?

Is there other way to identify which row is which,because exactly the JOIN column is messed up. — Mihai, Feb 04 '14 at 11:04
Not really - that's all the fields that are in the tables. Although I will only ever be selling a unique item once at a time (obviously) so that example above I bought the item at 17.59 sold it at 18.09 then bought it again at 18.53 - so the second bought record will always be after the listingBought time of the first record in the Sold table if that helps? — CPB07, Feb 04 '14 at 11:10

score 1 · Answer 1 · answered Oct 08 '14 at 08:14

1

UPDATE A SET A.BIDID=B.BIDID
FROM SOLD A JOIN(SELECT BIDID,ITEMID,BIDTIME FROM BOUGHT)B
ON A.ITEMID=B.ITEMID
AND A.LISTINGBOUGHT>B.BIDTIME
AND A.BIDID<>B.BIDID
WHERE A.LISTINGBOUGHT>B.BIDTIME

answered Oct 08 '14 at 08:14

PP006

681
7
17

SQL update column with correct ID from different table

1 Answers1