How to make a string by loop in Oracle SQL

user_12957467 0
Viewed 116

I'm here a newbie with a question seeking your help. So many thanks in advance!

I've got a table like this.

enter image description here

It's actually a string from A to B to C to D. And by the 'Start date', A is picked as the 'original one' as it is the first to appear. By that requirement, the table should be fixed like this

enter image description here

There are other strings like this in the table and I have to identify and string them correctly all. Anyone here may help me to do it on SQL Oracle? I cannot work it out on a loop. Thanks for your care much!

oracle sql loops string
1 Answers

You can use the HIERARCHY query to update the table with the desired value.

Oracle data setup:

SQL> select * from your_table;

START_DAT START_  END_
--------- ------- ----
01-JAN-20 A       B
02-JAN-20 B       C
03-JAN-20 C       D
01-JAN-20 X       Y
02-JAN-20 Y       Z

SQL>

Query to update the data:

SQL> UPDATE YOUR_TABLE YT
  2  SET YT.START_ = COALESCE(
  3      ( SELECT MIN(START_) KEEP(DENSE_RANK FIRST ORDER BY START_DATE)
  4          FROM YOUR_TABLE T
  5          CONNECT BY PRIOR START_ = END_
  6          START WITH END_ = YT.END_
  7      ), YT.START_);

5 rows updated.

SQL>

Updated data:

SQL> select * from your_table;

START_DAT START_  END_
--------- ------- ----
01-JAN-20 A       B
02-JAN-20 A       C
03-JAN-20 A       D
01-JAN-20 X       Y
02-JAN-20 X       Z

SQL>

Cheers!!

user_115656290
Related