Sql - Update Fierld With Max Date from Another Table

Update date field from MAX date in another table

Either:

UPDATE table1 t1
SET bill_attempt = 
      ( SELECT MAX(event_time) 
        FROM table2 t2
        WHERE t1.msisdn = t2.msisdn
      )

or:

UPDATE
      table1 t1
  JOIN 
      ( SELECT msisdn
             , MAX(event_time) AS event_time
        FROM table2 
        GROUP BY msisdn
      ) AS t2
  ON t1.msisdn = t2.msisdn
SET t1.bill_attempt = t2.event_time 

Updating a table with the max date of another table

this should get you started (Here the MAX function is the aggregate function and not the analytic function):

UPDATE table_a
   SET status = (SELECT MAX(table_b.status) 
                        KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
                   FROM table_b
                  WHERE table_a.location = table_b.location
                    AND table_b.statusdate <= table_a.trandate);

This will update all the rows in table_a, even if there is no prior row in table_b, updating the status to NULL in that case. If you only want to update the rows in table_a that have a corresponding match in table_b you can add a filter:

UPDATE table_a
   SET status = (SELECT MAX(table_b.status) 
                        KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
                   FROM table_b
                  WHERE table_a.location = table_b.location
                    AND table_b.statusdate <= table_a.trandate)
 WHERE EXISTS (SELECT NULL
                 FROM table_b
                WHERE table_a.location = table_b.location
                  AND table_b.statusdate <= table_a.trandate);

PL/SQL - Update Rows with Max Date Using Value from another table

So you want the status updated on the most recent item_updates record. You can do:

update item_updates iu
    set item_status = (select i.item_status from items where i.item_id = iu.item_id)
    where iu.effective_date = (select max(iu2.effective_date)
                               from item_updates iu2
                               where iu2.item_id = iu.item_id
                              );

Netezza - update one table with max data from another table

I don't have access to Netezza, but a usual format would be to use a correlated sub-query.

That is, instead of including TABLE_A again in the query, you refer to the outer reference to TABLE_A...

update
  TABLE_A
set
  PRICE = (
    select max(b.PRICE) 
      from TABLE_B b
     where TABLE_A.ID_NO         =    b.ID_NO
       and TABLE_A.ENTRY_DATE between b.START_DATE and b.END_DATE
  )

In this way, the correlated-sub-query is essentially invoked once for each row in TABLE_A and that invocation uses the current row from TABLE_A as its parameters.

An alternative could be...

update
  TABLE_A
set
  PRICE = revised.PRICE
from
(
      select a.ID_NO, a.ENTRY_DATE, max(b.PRICE) AS PRICE
        from TABLE_B b
  inner join TABLE_A a on a.ID_NO=b.ID_NO
       where a.ENTRY_DATE between b.START_DATE and b.END_DATE
    group by a.ID_NO, a.ENTRY_DATE
)
  AS revised
where
      TABLE_A.ID_NO      = revised.ID_NO
  and TABLE_A.ENTRY_DATE = revised.ENTRY_DATE

Result of a column based on the max date of another column

You can use row_number() :

SELECT t.*
FROM (SELECT e.BW_ID, oe.TEXT_1 AS Employee_Status, oe.text_1_eff_date AS Effective_Date,
             ROW_NUMBER() OVER (PARTITION BY e.BW_ID ORDER BY oe.text_1_eff_date) AS SEQ
      FROM EMPLOYEE e INNER JOIN 
           ORG_EMPLOYMENT oe 
           ON e.BW_ID = oe.BW_ID
      WHERE e.CONTROLLED_GROUP_STATUS = 'active'
     ) t
WHERE SEQ = 1;

MySQL - Update/Set a column in one table equal to MAX value from another table

How about a subselect?

UPDATE project
SET 
    project.i-date = (
        SELECT MAX(f-date)
        FROM schedule
        WHERE schedule.site = project.site
    ),
    project.status = 'complete'
WHERE project.site = 'site123'

Update field in table with the max value in another table, with the foreign key

This one works in Access 2007. I think it should work in 2003 also.

UPDATE C
SET mostRecentDate = DMax("theDate", "L", "c_id = " & C.c_id);

Beware, if you have a c_id value in C which is not present in L, mostRecentDate will be replaced by Null for that row in C. If you want to prevent that, it will take more work.

However I don't see the value of storing those max date values in table C. You already know you can can retrieve them from table L any time you need them with a GROUP BY query of L.

---

Related Topics