How to set the earliest date [message #669806] |
Mon, 14 May 2018 11:15 |
|
KavitaSSwami
Messages: 7 Registered: May 2018
|
Junior Member |
|
|
how to write a query to -
Set table1.date_column value to the earliest date of table2.date_column for all records on table2 that share the same column in table1 and table2
|
|
|
|
Re: How to set the earliest date [message #669812 is a reply to message #669806] |
Mon, 14 May 2018 12:35 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
In its simplest form, you can do an update from a select (provided that the keys are matching).
Here's a quick and dirty example.
create table table1 (table1_id number primary key, create_ts date);
create table table2 (table1_id number references table1 (table1_id), table2_id number, create_ts date);
alter table table2 add constraint secondary_pk primary key (table1_id, table2_id);
insert into table1 values (1, trunc(sysdate-2));
insert into table1 values (2, trunc(sysdate-1));
commit;
insert into table2 values (1, 1, trunc(sysdate));
insert into table2 values (1, 2, trunc(sysdate));
insert into table2 values (2, 1, trunc(sysdate));
insert into table2 values (2, 2, trunc(sysdate));
insert into table2 values (2, 3, trunc(sysdate));
commit;
DEV1> select table1_id, table2_id, create_ts from table2;
TABLE1_ID TABLE2_ID CREATE_TS
---------- ---------- ---------
1 1 14-MAY-18
1 2 14-MAY-18
2 1 14-MAY-18
2 2 14-MAY-18
2 3 14-MAY-18
Now for the update:
update (
select
table1.create_ts as tb1_create_ts,
table2.create_ts as tb2_create_ts
from
table1,
table2
where
table2.table1_id = table1.table1_id)
set
tb2_create_ts = tb1_create_ts;
DEV1> select table1_id, table2_id, create_ts from table2;
TABLE1_ID TABLE2_ID CREATE_TS
---------- ---------- ---------
1 1 12-MAY-18
1 2 12-MAY-18
2 1 13-MAY-18
2 2 13-MAY-18
2 3 13-MAY-18
JP
[Updated on: Mon, 14 May 2018 12:38] Report message to a moderator
|
|
|
|
|
|
|