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
Sqlstate[01000]: Warning: 1265 Data Truncated for Column
List All Employee'S Names and Their Managers by Manager Name Using an Inner Join
Counting the Number of Rows Returned by Stored Procedure
Ssis Date in Derived Column as Yyyy-Mm-Dd Format
How to Subtract One Day from Current Date Then Convert to String in Hive
Spring Data JPA Utf-8 Encoding Not Working
Postgresql How to Insert a Value With Double Quotes Inside Double Quotes
Grouping But With Keeping All Non-Null Values
I Want to Give Serial No in My Query According to Group
Oracle Pl/Sql String Compare Issue
Use Multiple Conflict_Target in on Conflict Clause
Comparing Two Columns Using SQL to Make Sure the Same Set of Values Are Present in Both
Subtract Two Columns from Two Tables With Group By
Sql Select Radius Search Based on Latitude Longitude
Mysql: Error Code: 1054. Unknown Column in 'Where Clause'