CODEWITHSUNDEEP
sqloracle-database
tsilvs
tsilvs

Reputation: 454

Update column value in all rows of a table on mod(rownum,10) = number

I have a table tab1 that looks like this: 

 col1 | col2 | col3
------|------|------
 abc  | 100  | text
 abc  | 100  | text
 abc  | 100  | text
 ...  | ...  | ...

I need to update col2 value in each row like this: 

update tab1 
    set col2 = 1,23
    when mod(rownum,10) = 1; 
update tab1 
    set col2 = 12,34
    when mod(rownum,10) = 2;
update tab1 
    set col2 = 123,45
    when mod(rownum,10) = 3;

and etc. until when mod(rownum,10) = 9.

But obviously this query doesn't work, and the reason is that rownum always returns 1 in this situation, afaik. However, I've got the correct last digits for each row number with select mod(rownum,10) as lastDig from tab1 query. But I don't understand how to use the result of this select for my update when conditions.

Could you please provide an example of a query that will do the job in this situation? Do I need to use a subquery or select in a temporary table? Please explain. I'm a junior frontend guy, but I need to create a demo table this way. I believe, pl/sql is v10, as well as PL/SQL Developer.

Result wanted looks like this:

 col1 | col2  | col3
------|-------|------
 abc  | 1.23  | text
 abc  | 12.34 | text
 abc  | 123.45| text
 ...  | ...   | ...

Upvotes: 2

Views: 1110

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You have not told us if there is a specific order in which you want to treat rows as 1,2,3 .. If there is indeed an order, then ROWNUM is unreliable and may not work, you would need row_number() with a specific order by column. That can be combined with a MERGE statement.

MERGE INTO tab1 tgt USING (
    SELECT
            CASE mod( ROW_NUMBER() OVER(
                ORDER BY
                    col1 -- the column which is in order and unique
            ),10)
                WHEN 1   THEN 1.23
                WHEN 2   THEN 12.34
                WHEN 3   THEN 123.45
                --..
                --.. 9
            ELSE col2
        AS col2
    FROM
        tab1 t
)
src ON ( tgt.rowid = src.rowid ) --use primary key/unique key if there is one instead of rowid
WHEN MATCHED THEN UPDATE SET tgt.col2 = src.col2;

Demo

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use CASE expression or DECODE:

update tab1 
set col2 =  CASE mod(rownum,10) WHEN 1 THEN 1.23
                                WHEN 2 THEN 12.34
                                WHEN 3 THEN 123.45
                                -- ...
                                ELSE col2
            END
-- WHERE ...

UPDATE tab1
SET col2 = DECODE(mod(rownum,10), 1, 1.23, 2, 12.34, 3, 123.45, ..., col2)
-- WHERE ...;

DBFiddle Demo

Upvotes: 1

Related Questions