OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » nested query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

nested query [message #7421]

Thu, 12 June 2003 12:28

WALID
Messages: 31
Registered: August 2002

Member

I need to update two columns in one table that has historical data with serial numbers as primary key with updated timestamps. For each serial number I could have many rows with different time stamps. The condition to update the columns that have all serials with updated time stamp less than sysdate-365 (one year old). Any help is appreciated.
I tried the following query, put returned all serials with dates that don't meat the sysdate-365 ceiteria:

update fmserhis t1 set t1.archstat =n'D', t1.archtms = sysdate where exists
(select serno from fmserhis t2 where t2.serno = t1.serno and t2.updttms <= sysdate-365)

Report message to a moderator

Re: nested query [message #7423 is a reply to message #7421]

Thu, 12 June 2003 13:07

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I would just do something like:

update fmserhis
   set archstat = 'D',
       archtms = sysdate
 where serno not in (select serno
                       from fmserhis
                      where updttms <= add_months(updttms, -12);

Get into the habit of not using 365 days in a query - remember that not every year has 365 days. The add_months function is much safer.

Report message to a moderator

Re: nested query (correction) [message #7424 is a reply to message #7423]

Thu, 12 June 2003 13:09

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

add_months(sysdate, -12)

Report message to a moderator

Re: nested query (correction) [message #7425 is a reply to message #7424]

Thu, 12 June 2003 13:58

WALID
Messages: 31
Registered: August 2002

Member

As you can see from the result set below; I still need to exclude units that does not meet the date criteria - they shows up because one or more rows for that unit met the criterial. What else I need to do that? Thanks.
102TSTB01 7/31/2001 2:02:43 PM
102TSTB00 7/31/2001 2:02:43 PM
101TSTD04 7/31/2002 1:28:47 PM
101TSTD10 7/31/2002 2:02:25 PM
101TSTD10 7/31/2002 2:02:34 PM
101TSTD10 7/31/2002 2:02:43 PM
101TSTD10 7/31/2001 2:02:43 PM
101TSTD09 7/31/2001 1:28:47 PM
101TSTD04 7/31/2001 1:28:47 PM
102TSTD10 7/31/2001 2:02:43 PM
102TSTB00 7/31/2001 2:02:43 PM
102TSTB01 7/31/2001 2:02:43 PM
101TSTD09 7/31/2002 1:28:47 PM
101TSTD10 7/31/2002 1:00:41 PM
101TSTD09 7/31/2002 1:28:47 PM
101TSTD09 7/31/2002 1:28:47 PM
101TSTD09 7/31/2002 1:28:47 PM
101TSTD04 7/31/2002 1:28:47 PM
101TSTD09 7/31/2002 1:00:41 PM
101TSTD09 7/31/2002 1:00:26 PM
101TSTD10 7/31/2002 1:00:26 PM
101TSTD04 7/31/2002 1:00:38 PM
101TSTD04 7/31/2002 1:00:23 PM

Report message to a moderator

Re: nested query (correction) [message #7426 is a reply to message #7425]

Thu, 12 June 2003 14:24

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

What is it about the query I supplied (with the correction) that doesn't work for you?

It will update any row where there is no update_date for that serno older than a year.

In other words, a row will be updated if all update_dates for that serno are within the last year.

Report message to a moderator

Re: nested query (correction) [message #7427 is a reply to message #7426]

Thu, 12 June 2003 14:41

WALID
Messages: 31
Registered: August 2002

Member

As you can see from the results below. The query returned data that I want to excude. One row in the first serno met the criteria but not the others. The second serno rows met the criteria, that I want to keep.
101TSTD04 7/31/2002 1:28:47 PM
101TSTD04 7/31/2001 1:28:47 PM
101TSTD04 7/31/2002 1:00:38 PM
101TSTD04 7/31/2002 1:28:47 PM
101TSTD04 7/31/2002 1:00:23 PM

102TSTB01 7/31/2001 2:02:43 PM
102TSTB01 7/31/2001 2:02:43 PM

Report message to a moderator

Re: nested query (correction) [message #7429 is a reply to message #7427]

Thu, 12 June 2003 15:57

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

From your original message:

"The condition to update the columns that have all serials with updated time stamp less than sysdate-365 (one year old)."

To me, this means that you would not update any rows for 101TSTD04, because there is that one row for that serno that is older than a year.

Neither would you update 102TSTB01, because all of its rows are more than one year old.

So, since I am not understanding what you are trying to do, let's take this simple example. Here are all the rows in the table:

sql>select * from h order by id;
 
       ID SERNO     UPDATED
--------- --------- ----------
        1 1         07/31/2001
        2 1         08/31/2002
        3 1         09/30/2002
        4 2         01/01/2000
        5 2         01/01/2001
        6 3         05/31/2003
        7 3         06/01/2003
 
7 rows selected.

I have added the id column so we can identify which rows we are talking about.

For serno = 1, there is one row older than a year (1) and two rows within the year (2, 3).

For serno = 2, both rows (4, 5) are older than a year.

For serno = 3, both rows (6, 7) are less than a year old.

Which rows get updated?

Report message to a moderator

Re: nested query (correction) [message #7441 is a reply to message #7429]

Fri, 13 June 2003 07:33

WALID
Messages: 31
Registered: August 2002

Member

Only serno=2 should be updated. All rows for a serno must meet the condition to be updated.

Thanks for all your help.

Report message to a moderator

Fri, 13 June 2003 11:06

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

OK, I understand now. Your requirement is actually the opposite of how you originally stated it. You want to update sernos where all rows for that serno are older than one year.

update fmserhis t1   
   set archstat = 'D',       
       archtms = sysdate 
 where updttms < add_months(sysdate, -12)
   and not exists (select null
                     from fmserhis t2
                    where t2.serno = t1.serno
                      and t2.updttms >= add_months(sysdate, -12));

Report message to a moderator

Fri, 13 June 2003 11:10

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

And an alternate version:

update fmserhis   
   set archstat = 'D',       
       archtms = sysdate 
 where serno in (select serno
                   from fmserhis
                  group by serno
                 having max(updttms) < add_months(sysdate, -12));

Report message to a moderator

Previous Topic:	detecting existance of temp tables
Next Topic:	Scheduling a job

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 12:13:29 CDT 2024