CODEWITHSUNDEEP
sqlsql-server-ce
seph
seph

Reputation: 694

Update entry according to oldest entry in other table

I have the following 2 tables (running on SQL Server Compact Edition, Version 3.5.8080.0):

tblInventory

|                     GUID_INVENTORYID |               DAT_DEADLINE |
|--------------------------------------|----------------------------|
| 13720A5F-F1F0-4420-AA9C-11CB955B86AA |         2014-05-01         |


tblInventoryDetails

|              GUID_INVENTORYDETAILSID |                     GUID_INVENTORYID |               DAT_INSERTED |
|--------------------------------------|--------------------------------------|----------------------------|
| EE222BCE-8182-4C70-A07C-7568838055A7 | 13720A5F-F1F0-4420-AA9C-11CB955B86AA |         2014-05-21         |
| 9BD8A3DC-31BE-418D-9F80-B88A50A58D31 | 13720A5F-F1F0-4420-AA9C-11CB955B86AA |         2014-05-10         |



There can be of course more then two entries in the tblInventoryDetails; All entries are linked to one entry in the tblInventory.

Now I want to update dat_Deadline according to the oldest dat_Inserted in tblInventoryDetails (which would be '2014-05-10' in this case).

I know that I can get the oldest entry like this:

SELECT TOP (1) dat_Inserted 
FROM tblInventoryDetails
WHERE GUID_INVENTORYID = @GUID_INVENTORYID
ORDER BY dat_Inserted

But how can I user that in my UPDATE-statement?

I also made a SQLFiddle for this.

Upvotes: 0

Views: 56

Answers (2)

georstef
georstef

Reputation: 1388

Try this way:

UPDATE
  tblInventory
SET
  DAT_DEADLINE = 
  (
   SELECT MIN(DAT_INSERTED)
   FROM tblInventoryDetails 
   WHERE tblInventoryDetails.GUID_INVENTORYID = tblInventory.GUID_INVENTORYID
  )
WHERE
  tblInventory.GUID_INVENTORYID = '13720A5F-F1F0-4420-AA9C-11CB955B86AA'

another solution would be:

DECLARE @min_date datetime
SET @min_date = 
  (
   SELECT MIN(DAT_INSERTED)
   FROM tblInventoryDetails 
   WHERE tblInventoryDetails.GUID_INVENTORYID = '13720A5F-F1F0-4420-AA9C-11CB955B86AA'
  )

UPDATE
  tblInventory
SET
  DAT_DEADLINE = @min_date
WHERE
  tblInventory.GUID_INVENTORYID = '13720A5F-F1F0-4420-AA9C-11CB955B86AA'

Since it was pointed out that nested selects and declare do not work in SQL Server Compact Edition. A solution of two separate statements would more appropriate for this question. First get the min date value and then update the proper table explicitly

Upvotes: 2

h.s.o.b.s
h.s.o.b.s

Reputation: 1319

I'm not a SQL guru, but a subquery like this may help you.

UPDATE tblInventory
SET dat_deadline = (
  SELECT MIN(dat_Inserted)
  FROM tblInventoryDetails
  WHERE guid_InventoryID = '13720A5F-F1F0-4420-AA9C-11CB955B86AA'
);

Upvotes: 0

Related Questions