CODEWITHSUNDEEP
sqloracle-database
SQL Learner
SQL Learner

Reputation: 55

Compare the column values and update another column in Oracle

I have a scenario where we have first two entries for each test. Meaning for each test we have two entries, now I want to compare the test ranks and update the LoginID column in my table.

Sample data:

RANK | TestID | LoginID
-----+--------+---------
234     14       null   
235     14       null   
330     15       null   
331     15       null   
223     18       null   
222     18       null   
500     20       null
501     20       null

In the above table, I have two entries for each testId, now I need to compare the RANK column of the corresponding TESTID rows, and update the LoginId column with 1 and 2.

Finally I am looking for output like follows:

RANK | TestID | LoginID
-----+--------+---------
234     14       1  
235     14       2  
330     15       1  
331     15       2  
223     18       2  
222     18       1  
500     20       1
501     20       2

Can anyone suggest which is better way to achieve this? I tried with min() and max(), with Case (When /then) concept, but the query is not returning the expected results.

Upvotes: 1

Views: 1149

Answers (3)

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

Try this: 

UPDATE
 yourTable T,
 (
    SELECT
         TestID
        ,MIN( RANK) AS firstRank
        ,MAX( RANK) AS secondRank
    FROM
        yourTable
    GROUP BY
        TestID
) A
SET  RANK  = 
    CASE WHEN RANK = A.firstRank  THEN  1
         WHEN RANK = A.secondRank THEN  2
         END

Upvotes: 1

toonice
toonice

Reputation: 2246

Please try...

UPDATE tableName
SET LoginID = ( RANK MOD 2 ) + 1;

Note : This assumes that the lesser LoginID will always correspond with the lesser RANK for each TestID.

Further reading...

https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_mod

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can use MERGE with window function row_number:

merge into your_table t
using (
    select t.*, 
        row_number() over (partition by testId order by rank) as rn
    from your_table t
) s on (
    t.rank = s.rank
    and t.testId = s.testId
    )
when matched then update set t.loginId = s.rn;

Upvotes: 2

Related Questions