CODEWITHSUNDEEP
sqloracle-database
Mukesh Dhiman
Mukesh Dhiman

Reputation: 99

Update a column taking data from same column

I have a table and data looks like this I want to fill the dates eg:-1-5 should have date 2/8/2012 and 6-9 should have dates :- 8-Feb

1   2/8/2012    G8-212
2   null            G8 711
3   null            G8-719
4   null            G8 331
5   null            G8 320
6   8-Feb           G8 172
7   null            G8 606
8   null            G8 176
9   null            G8 372

End result should be something like this

1   2/8/2012    G8-212
2   2/8/2012    G8 711
3   2/8/2012    G8-719
4   2/8/2012    G8 331
5   2/8/2012    G8 320
6   8-Feb           G8 172
7   8-Feb           G8 606
8   8-Feb           G8 176
9   8-Feb           G8 372

Upvotes: 0

Views: 93

Answers (3)

Florin Ghita
Florin Ghita

Reputation: 17643

Something like this:

select 
  id, 
  nvl(date_column,last_value(date_column ignore nulls) over (order by id)) as date_col, 
  code
from your_table
order by id;

see a Fiddle here

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try using merge:

merge into YourTable a
using (
    SELECT ID,
    ColumnName,
    nvl(ColumnName,last_value(ColumnName ignore nulls) over (order by ID)) as ColumnName_A
  FROM YourTable 
  ) b
on  (a.ID = b.ID)
when matched then 
  update set a.ColumnName = NVL(b.ColumnName, b.ColumnName_A)

Upvotes: 1

crazylpfan
crazylpfan

Reputation: 1098

You could try doing something like this, assuming you are sorting on the first column: (NOTE: I'm not familiar with the Oracle/PLSQL syntax, so take these queries with a grain of salt.)

First, you need to get all the indexes of the non-null rows and their value:

SELECT col1, col2
FROM table1
WHERE col2 IS NOT NULL

Now, loop through the results of this query and update the column as needed:

DECLARE
     CURSOR v_cols IS
          SELECT col1, col2 FROM table1 WHERE col2 IS NOT NULL;
     Int previousIndex := 0;
BEGIN
     FOR v_col IN v_cols LOOP
          UPDATE col2
          SET col2 = v_col.col2
          WHERE col1 < v_col.col1 AND col1 > previousIndex;
          previousIndex := v_col.col1;
     END LOOP;
END;

This is obviously untested, so let me know if it helps!

Upvotes: -1

Related Questions