0

Consider i have two table train_reserve and reserve:

train_reserve:

ChangeId C_Id Process Download trainId Status SDate EDate Book_date L_date BookId
1 1001 1 A 1995 B 05-APR-22 06-APR-22 10-MAR-22 11-MAR-22 111
2 1001 2 B 1995 M 05-APR-22 08-APR-22 10-MAR-22 11-MAR-22 111
3 1002 1 B 1995 B 12-APR-22 14-APR-22 10-MAR-22 11-MAR-22 222
4 1002 2 C 1995 M 12-APR-22 13-APR-22 10-MAR-22 11-MAR-22 222
5 1003 1 A 1995 B 25-MAY-22 25-MAY-22 10-MAR-22 11-MAR-22 333
6 1004 1 A 1995 B 19-MAR-22 20-MAR-22 10-MAR-22 11-MAR-22 444
7 1004 1 B 1995 B 19-MAR-22 20-MAR-22 10-MAR-22 11-MAR-22 555

reserve:

C_Id trainId SDate EDate L_date BookId
1001 1995 05-APR-22 08-APR-22 11-MAR-22 111
1002 1995 12-APR-22 13-APR-22 11-MAR-22 222
1003 1995 25-MAY-22 25-MAY-22 11-MAR-22 333
1004 1995 19-MAR-22 20-MAR-22 11-MAR-22 444
1005 1995 19-MAR-22 20-MAR-22 11-MAR-22 555

Below is the input from user: C_id=1, Process=(1,2), Download=(A,B,C), trainId=1995, Status=(B),Sdate=null,Edate=null,Book_date>='10-MAR-22',L_date=null.

User want to update BookId=null in both tables when C_id>=1001 and Status is B only . i.e I want below output:

train_reserve:

ChangeId C_Id Process Download trainId Status SDate EDate Book_date L_date BookId
1 1001 1 A 1995 B 05-APR-22 06-APR-22 10-MAR-22 11-MAR-22 111
2 1001 2 B 1995 M 05-APR-22 08-APR-22 10-MAR-22 11-MAR-22 111
3 1002 1 B 1995 B 12-APR-22 14-APR-22 10-MAR-22 11-MAR-22 222
4 1002 2 C 1995 M 12-APR-22 13-APR-22 10-MAR-22 11-MAR-22 222
5 1003 1 A 1995 B 25-MAY-22 25-MAY-22 10-MAR-22 11-MAR-22 null
6 1004 1 A 1995 B 19-MAR-22 20-MAR-22 10-MAR-22 11-MAR-22 null
7 1004 1 B 1995 B 19-MAR-22 20-MAR-22 10-MAR-22 11-MAR-22 null

reserve:

C_Id trainId SDate EDate L_date BookId
1001 1995 05-APR-22 08-APR-22 11-MAR-22 111
1002 1995 12-APR-22 13-APR-22 11-MAR-22 222
1003 1995 25-MAY-22 25-MAY-22 11-MAR-22 null
1004 1995 19-MAR-22 20-MAR-22 11-MAR-22 null
1005 1995 19-MAR-22 20-MAR-22 11-MAR-22 null

I am currently using two update statements as below

update train_reserve a
   set a.BookId=null
 where a.C_Id>=1001 
   and a.trainId=1995 
   and a.Process in (1,2) 
   and a.Download in ('A','B','C') 
   and a.Status='B' 
   and a.Book_date>='10-MAR-22' 
   and not exists (select 1 
                     from train_reserve b 
                    where a.C_Id = b.C_Id 
                      and b.Status='M');

update reserve 
   set BookId = null 
 where a.C_Id in (select a.C_Id 
                    from train_reserve a 
                   where a.C_Id >= 1001 
                     and a.trainId=1995 
                     and a.Process in (1,2) 
                     and a.Download in ('A','B','C') 
                     and a.Status='B' 
                     and a.Book_date>='10-MAR-22' 
                     and not exists (select 1 
                                       from train_reserve b 
                                      where a.C_Id = b.C_Id 
                                        and b.Status='M'));

But second query above takes long time to update since i am fetching data from 1st table then updating reserve table.

Is there optimized way to achieve above result?
MT0
  • 143,790
  • 11
  • 59
  • 117
Coder001
  • 1
  • 1
  • Please post the [execution plan](https://stackoverflow.com/a/34975420/4808122) of the second statement. Check the link for other information required, e.g. the size of the table and how many rows do you expect to be updated. Available indexes / partitioning etc. – Marmite Bomber Mar 15 '22 at 13:19
  • for Last 3 rows the BookId is expected to be updated as null, because for the last 3 rows a.C_Id >= 1001 and a.trainId=1995 and a.Process in (1,2) and a.Download in ('A','B','C') and a.Status='B' and a.Book_date>='10-MAR-22' . – Coder001 Mar 15 '22 at 13:24

0 Answers0