Update table with inner join

Question

I have two tables shown below

BidID   CreatedDate              BidVersionNumber
2       2018-05-17 04:35:40.320    AB25443/01-01
3       2018-06-11 03:36:59.977    AB25443/01-01

Second Table

BidVersionNumber       CreatedDate        BidId
 AB25443/01-01       5/17/2018 4:35:40
 AB25443/01-01       5/17/2018 4:41:10

I want to update the BidId of the second table by joining with the first table on BidVersionNumber but since the BidVersionNumber is the same I also want to match on CreatedDate joining just on the 4:35:40 part of the time. Can someone please tell me how to join on two columns.

Desired output

BidVersionNumber       CreatedDate        BidId
 AB25443/01-01       5/17/2018 4:35:40     2
 AB25443/01-01       5/17/2018 4:41:10     3

*joining just on the 4:35:40* but this doesn't work for the second row. How does it get BidId = 3? — S3S, Jan 31 '19 at 20:43
@scsimon he means that he wants to join on `CreatedDate` but ignore the milliseconds in the JOIN. — Tab Alleman, Jan 31 '19 at 21:18
Right @TabAlleman but for that second row, the hour, minute, and second doesn't match. Or eve the date for that matter, but expected output has it updated — S3S, Jan 31 '19 at 21:22

Gordon Linoff · Accepted Answer · 2019-01-31T20:50:17.333

0

The tricky part here is the time comparison. One method of doing this is by subtracting a second from the t1 time and essentially using between:

update t2
    set bidId = t1.BidId
    from table2 t2 join
         table1 t1
         on t2.BidVersionNumber = t1.BidVersionNumber and
            t2.CreatedDate <= t1.CreatedDate and
            t2.CreatedDate > dateadd(second, -1, t1.CreatedDate);

edited Jan 31 '19 at 20:50

answered Jan 31 '19 at 20:41

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon..Always helpful :) – SP1 Jan 31 '19 at 21:13

Update table with inner join

1 Answers1