Home » SQL & PL/SQL » SQL & PL/SQL » How to set the earliest date
How to set the earliest date [message #669806] Mon, 14 May 2018 11:15 Go to next message
KavitaSSwami
Messages: 5
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 #669809 is a reply to message #669806] Mon, 14 May 2018 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26004
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/mv/msg/204650/669643/#msg_669643

If you won't answer our questions then we can't answer your questions.

post CREATE TABLE statements for both TABLE1 & TABLE2
Re: How to set the earliest date [message #669812 is a reply to message #669806] Mon, 14 May 2018 12:35 Go to previous messageGo to next message
JPBoileau
Messages: 24
Registered: September 2017
Junior 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

Re: How to set the earliest date [message #669815 is a reply to message #669812] Mon, 14 May 2018 14:46 Go to previous messageGo to next message
KavitaSSwami
Messages: 5
Registered: May 2018
Junior Member
Got it...

Thanks a lot for the valuable answer.
Re: How to set the earliest date [message #669816 is a reply to message #669815] Mon, 14 May 2018 15:02 Go to previous messageGo to next message
BlackSwan
Messages: 26004
Registered: January 2009
Location: SoCal
Senior Member
What exactly does below mean?
>Set table1.date_column value to the earliest date of table2.date_column

Where in posted SQL above does any mention of "earliest" occur?

Just because SQL returns data, it does not mean that the returned data correctly answers the posted question.
Re: How to set the earliest date [message #669817 is a reply to message #669816] Mon, 14 May 2018 15:17 Go to previous messageGo to next message
JPBoileau
Messages: 24
Registered: September 2017
Junior Member
You're right, I overlooked that part. I was trying to show an UPDATE from SELECT.

JP
Re: How to set the earliest date [message #669818 is a reply to message #669817] Mon, 14 May 2018 15:22 Go to previous message
BlackSwan
Messages: 26004
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, Aim!

Earliest overall?
Earliest for specific ID?

If OP were to actually follow Posting Guidelines, the requirements might be less ambiguous.

Previous Topic: Query to find common column in more then two tables
Next Topic: Procedure
Goto Forum:
  


Current Time: Fri May 25 15:15:05 CDT 2018