CODEWITHSUNDEEP
sqloracle-database
lighter
lighter

Reputation: 2806

update column with other column

I use PL/SQL, This is my table

 a   |    b    |    c   |   d
-----------------------------
 1   |    1    |    2   |
 1   |    2    |    2   |
 1   |    2    |    2   |
 1   |    3    |    2   |
 1   |    3    |    2   | 
 2   |    2    |    2   |
 2   |    4    |    2   |
 2   |    5    |    1   |

And it will become to 

 a   |    b    |    c   |   d
-----------------------------
 1   |    1    |    2   |  1
 1   |    2    |    2   |  2
 1   |    2    |    2   |  2
 1   |    3    |    2   |  3
 1   |    3    |    2   |  3
 2   |    2    |    2   |  1
 2   |    4    |    2   |  2
 2   |    5    |    1   |

d column will start from 1 and then plus 1

if b column start change,

and if a column become different, the d column will repeat start from 1.

I want to update d column with c equal 2.

I know how to use ROW_NUMBER() OVER (PARTITION BY ), but that I have two column relationship. I have no idea.

How to do that? Thanks appreciate.

Upvotes: 0

Views: 117

Answers (2)

Jon Senchyna
Jon Senchyna

Reputation: 8047

Sicne you cannot use windowed functions in a SET clause, I believe you will need to use a nested query to get your new values for d. The following should work for you:

UPDATE T
SET    T.d = D.d
FROM Table1 T
-- Join on a subquery to get the new 'd' values:
INNER JOIN (
  SELECT a, b, c, -- Get the identifiers for this row
         -- Get the new value for d
        dense_rank() over (partition by a 
                           order by b) as d
  FROM Table1
) D -- Map the new 'd' values back to the appropriate row
ON T.a = D.a and T.b = D.b AND T.c = D.c

SQLFiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Unfortunately, data manipulations in Oracle updates are a pain.

This does what you want:

update table t
    set d = (select count(distinct b)
             from table t2
             where t2.a = t.a and
                   t2.b <= t.b
            ) ;

In a select query, you can do this with dense_rank().

Upvotes: 2

Related Questions