Home » SQL & PL/SQL » SQL & PL/SQL » Update values "in groups" (Oracle 12c, 12.1.0.2.0, MS Windows 7)
Update values "in groups" [message #663582] Fri, 09 June 2017 03:13 Go to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hello!

I need assistance. Here's what I have:
create table test
( rid        number,
  sta        varchar2(3),
  date_from  date,
  date_to    date
);

insert into test (rid, sta, date_from, date_to) 
select 3468, 'RVT', date '2015-08-19', date '2015-10-01' from dual union
select 3468, 'SMN', null             , date '2015-10-01' from dual union
select 3468, 'RVT', date '2015-10-01', date '2016-01-01' from dual union
select 3468, 'SMN', null,              date '2016-01-01' from dual union
select 3468, 'RVT', date '2016-01-01', date '2016-02-19' from dual union
select 3468, 'SMN', null,              date '2016-02-19' from dual union
--
select 5931, 'RVT', date '2016-09-01', date '2017-01-01' from dual union
select 5931, 'SMN', null,              date '2017-01-01' from dual union
select 5931, 'RVT', date '2017-01-01', date '2017-01-10' from dual union
select 5931, 'RVT', date '2017-01-10', date '2017-02-23' from dual union
select 5931, 'SMN', null,              date '2017-02-23' from dual;

SQL> select * from test
  2  order by rid, date_to, sta;

       RID STA DATE_FROM  DATE_TO
---------- --- ---------- ----------
      3468 RVT 19.08.2015 01.10.2015
      3468 SMN            01.10.2015
      3468 RVT 01.10.2015 01.01.2016
      3468 SMN            01.01.2016
      3468 RVT 01.01.2016 19.02.2016
      3468 SMN            19.02.2016
      5931 RVT 01.09.2016 01.01.2017
      5931 SMN            01.01.2017
      5931 RVT 01.01.2017 10.01.2017
      5931 RVT 10.01.2017 23.02.2017
      5931 SMN            23.02.2017

I have to fill DATE_FROM values for STA = SMN (they are all NULL).

Rows are grouped by RID, and - within that RID - by "pairs" of (one or more) RVT + one SMN. SMN's DATE_FROM should be set to the MIN(DATE_FROM) that belongs to its group's RVT. Unfortulately, there's no "group" indicator in the table (that says which RVTs and SMNs go together); these groups are identified by "ORDER BY" clause (order by rid, date_to, sta).

For example:
       
       RID STA DATE_FROM  DATE_TO
---------- --- ---------- ----------
      3468 RVT 19.08.2015 01.10.2015  --> MIN(DATE_FROM) for RVT in this group is 19.08.2015
      3468 SMN            01.10.2015  --> DATE_FROM should be 19.08.2015

or 
       RID STA DATE_FROM  DATE_TO
---------- --- ---------- ----------
      5931 RVT 01.01.2017 10.01.2017  --> MIN(DATE_FROM) of all RVT's in this group is 01.01.2017
      5931 RVT 10.01.2017 23.02.2017
      5931 SMN            23.02.2017  --> DATE_FROM should be 01.01.2017

Queries I managed to write don't work; it is the second group (in the above example) that makes problems. Here's one of my attempts; close, but not enough:
SQL> update test sr
  2     set sr.date_from =
  3            (select sr1.date_from
  4               from test sr1
  5              where     sr1.rid = sr.rid
  6                    and sr1.sta = 'RVT'
  7                    and sr1.date_from <> sr1.date_to
  8                    and sr1.date_to = sr.date_to)
  9   where sr.sta = 'SMN' and sr.date_from is null;

5 rows updated.

SQL> select * from test
  2  order by rid, date_to, sta;

       RID STA DATE_FROM  DATE_TO
---------- --- ---------- ----------
      3468 RVT 19.08.2015 01.10.2015
      3468 SMN 19.08.2015 01.10.2015
      3468 RVT 01.10.2015 01.01.2016
      3468 SMN 01.10.2015 01.01.2016
      3468 RVT 01.01.2016 19.02.2016
      3468 SMN 01.01.2016 19.02.2016
      5931 RVT 01.09.2016 01.01.2017
      5931 SMN 01.09.2016 01.01.2017
      5931 RVT 01.01.2017 10.01.2017
      5931 RVT 10.01.2017 23.02.2017
      5931 SMN 10.01.2017 23.02.2017  --> this is wrong; DATE_FROM should be 01.01.2017, not 10.01.2017

PL/SQL option I wrote (I can post it, if necessary) works OK, but it is dead slow as there are millions of rows I have to update so I'd much rather use SQL instead. Unfortunately, I'm incapable of writing it. I *feel* that the solution is here, somewhere, slightly out of reach, but I just can't see it.

Could someone assist, please?
Re: Update values "in groups" [message #663588 is a reply to message #663582] Fri, 09 June 2017 06:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Assuming date_to is same within group:

SELECT  RID,
        STA,
        NVL(
            DATE_FROM,
            MIN(DATE_FROM) OVER(PARTITION BY RID,DATE_TO ORDER BY STA)
           ) DATE_FROM,
        DATE_TO
  FROM  TEST T
/

       RID STA DATE_FROM DATE_TO
---------- --- --------- ---------
      3468 RVT 19-AUG-15 01-OCT-15
      3468 SMN 19-AUG-15 01-OCT-15
      3468 RVT 01-OCT-15 01-JAN-16
      3468 SMN 01-OCT-15 01-JAN-16
      3468 RVT 01-JAN-16 19-FEB-16
      3468 SMN 01-JAN-16 19-FEB-16
      5931 RVT 01-SEP-16 01-JAN-17
      5931 SMN 01-SEP-16 01-JAN-17
      5931 RVT 01-JAN-17 10-JAN-17
      5931 RVT 10-JAN-17 23-FEB-17
      5931 SMN 10-JAN-17 23-FEB-17

11 rows selected.

SQL> 

SY.



Re: Update values "in groups" [message #663589 is a reply to message #663588] Fri, 09 June 2017 07:03 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Solomon. However, that's wrong as I said in my initial message. The last row's DATE_FROM should be 01.01.2017, not 10.01.2017.
       RID STA DATE_FROM  DATE_TO
---------- --- ---------- ----------
      5931 RVT 01.01.2017 10.01.2017  --> MIN(DATE_FROM) of all RVT's in this group is 01.01.2017
      5931 RVT 10.01.2017 23.02.2017
      5931 SMN            23.02.2017  --> DATE_FROM should be 01.01.2017

Your (and my) query result in

5931 RVT 01-JAN-17 10-JAN-17
5931 RVT 10-JAN-17 23-FEB-17
5931 SMN 10-JAN-17 23-FEB-17 --> this should be 01.01.2017, not 10.01.2017
Re: Update values "in groups" [message #663590 is a reply to message #663588] Fri, 09 June 2017 07:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Ah, my assumption was wrong:

WITH T AS (
           SELECT  TEST.*,
                   CASE LAG(STA,1,'SMN') OVER(PARTITION BY RID ORDER BY DATE_TO,STA)
                     WHEN 'SMN' THEN DATE_FROM
                   END FIRST_RVT_DATE_FROM
             FROM  TEST
           )
SELECT  RID,
        STA,
        NVL(
            DATE_FROM,
            MAX(FIRST_RVT_DATE_FROM) OVER(PARTITION BY RID ORDER BY DATE_TO,STA)
           ) DATE_FROM,
        DATE_TO
  FROM  T
/

       RID STA DATE_FROM DATE_TO
---------- --- --------- ---------
      3468 RVT 19-AUG-15 01-OCT-15
      3468 SMN 19-AUG-15 01-OCT-15
      3468 RVT 01-OCT-15 01-JAN-16
      3468 SMN 01-OCT-15 01-JAN-16
      3468 RVT 01-JAN-16 19-FEB-16
      3468 SMN 01-JAN-16 19-FEB-16
      5931 RVT 01-SEP-16 01-JAN-17
      5931 SMN 01-SEP-16 01-JAN-17
      5931 RVT 01-JAN-17 10-JAN-17
      5931 RVT 10-JAN-17 23-FEB-17
      5931 SMN 01-JAN-17 23-FEB-17

11 rows selected.

SQL> 

SY.
Re: Update values "in groups" [message #663591 is a reply to message #663590] Fri, 09 June 2017 07:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, yes! That's it! Thank you very much!

(Looking at your query, it appears that it was a little bit further than just slightly out of reach.)
Re: Update values "in groups" [message #663592 is a reply to message #663591] Fri, 09 June 2017 07:33 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Littlefoot wrote on Fri, 09 June 2017 08:18


(Looking at your query, it appears that it was a little bit further than just slightly out of reach.)

Not really. It is, if you think about it, just a simplified variation of same start-of-group method. You can find several posts on using this method.

SY.
Previous Topic: Handling invalid identifier exception in plsql
Next Topic: Trigger to fill foreign key from another table
Goto Forum:
  


Current Time: Fri Mar 29 02:40:31 CDT 2024