x

Update rows in one table based on minimum date value from another table

Table A

HSID (int, PK)
EarliestTapStart (datetime)

Table B

TapID (int, PK) HSID
TapStart (datetime)

So, for each HSID, there are multiple TapIDs in table B (and multiple TapStart dates). I want to run an update query that will determine the earliest TapStart for each HSID in Table B and update EarliestTapStart in Table A (to the corresponding HSID, of course).

 Update [Table A] set EarliestTapStart = (Select Min(TapStart) from [Table B])

Above statement updates every record with the minimum date for all HSID.

How do I get the update to work so that each HSID is updated correctly?

Thanks very much for any assistance you can provide.

more ▼

asked Jul 25, 2012 at 05:09 PM in Default

avatar image

mbdunson
0 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Thanks sunil2711 for getting me on the right path. Here's the actual SQL that I used to make it work.

update tblA set EarliestTapStart = (select EarliestStartTime from (select HSID, min(StartTime) as EarliestStartTime from tblB group by HSID) mst where tblA.HSID = mst.HSID)

more ▼

answered Jul 25, 2012 at 08:26 PM

avatar image

mbdunson
0 1 1 2

(comments are locked)
10|1200 characters needed characters left

Try like this update [Table A] x set EarliestTapStart = ( select Tapstart from ( select tapid,min(tapstart) from [Table B] )y where x.HSID=y.HSID

If you are familer with CTE,YOu can do also with CTE's

more ▼

answered Jul 25, 2012 at 07:06 PM

avatar image

sunil2711
276 9 11 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x174

asked: Jul 25, 2012 at 05:09 PM

Seen: 3280 times

Last Updated: Jul 25, 2012 at 08:26 PM

Copyright 2017 Redgate Software. Privacy Policy