UPDATE column from one table to another

Question

I need to update a column in a table to the latest date/time combination from another table. How can I get the latest date/time combination from the one table and then update a column with that date in another table?

The two tables I am using are called dbo.DD and dbo.PurchaseOrders. The JOIN between the two tables are dbo.DueDate.XDORD = dbo.PurchaseOrders.PBPO AND dbo.DueDate.XDLINE = dbo.PurchaseOrders.PBSEQ. The columns from dbo.DueDate that I need the latest date/time from are dbo.DueDate.XDCCTD and dbo.DueDate.XDCCTT.

I need to set dbo.PurchaseOrders.PBDUE = dbo.DueDate.XDCURDT.I can't use an ORDER BY statement in the UPDATE statement, so I'm not sure how to do this. I know row_number sometimes works in these situations, but I'm unsure of how to implement.

LOL. I know Aaron, I'm sorry but I do not have control over the naming conventions. They do have "meaning" to me sometimes. — tsqln00b, Nov 11 '13 at 18:11
Kind of like my license plate has meaning to me sometimes - but not to anyone else, really, ever. — Aaron Bertrand, Nov 11 '13 at 18:16
I just wanted to show the working code. That code does work for me now. — tsqln00b, Nov 11 '13 at 18:27
I've added it to the answer, a more relevant spot for it. Code samples in the question should be what you've tried (or schema / sample data setup), accompanied with why what you tried didn't work. — Aaron Bertrand, Nov 11 '13 at 18:31

Aaron Bertrand · Accepted Answer · 2013-11-11T18:31:18.010

The general pattern is:

;WITH s AS 
(
  SELECT  
   key, -- may be multiple columns
   date_col, 
   rn = ROW_NUMBER() OVER  
   (
     PARTITION BY key -- again, may be multiple columns
     ORDER BY date_col DESC
   )
  FROM dbo.SourceTable
)
UPDATE d 
  SET d.date_col = s.date_col
  FROM dbo.DestinationTable AS d
  INNER JOIN s 
  ON d.key = s.key -- one more time, may need multiple columns here
WHERE s.rn = 1;

I didn't try to map your table names and columns because (a) I didn't get from your word problem which table was the source and which was the destination and (b) those column names look like alphabet soup and I would have screwed them up anyway.

Did seem though that the OP got this specific code working:

;WITH s AS 
    (
      SELECT  
       XDORD, XDLINE,
       XDCURDT,
       rn = ROW_NUMBER() OVER  
       (
         PARTITION BY XDORD, XDLINE
         ORDER BY XDCCTD DESC, XDCCTT desc
       )
      FROM dbo.DueDate
    )
    UPDATE d 
      SET d.PBDUE = s.XDCURDT
      FROM dbo.PurchaseOrders AS d
      INNER JOIN s 
      ON d.PBPO = s.XDORD AND d.PBSEQ = s.XDLINE
    WHERE s.rn = 1;

UPDATE column from one table to another

1 Answers1