CODEWITHSUNDEEP
sqloracle-databasesql-update
gaut
gaut

Reputation: 5958

SQL Update all rows for one column with a list of values

How can I set all rows of a column by manually typing the values?

My table has 3 rows, I want the column named mycolumn to have 3 values a, b and c (currently those values are NULL):

update mytable set mycolumn = ('a','b','c')
ORA-00907 missing right parenthesis

EDIT: my table is very simple, I have one column ID INT NOT NULL with values 1, 2, 3 and another column mycolumn with all NULL values and I want those values to become 'a' where ID = 1, 'b' where ID=2 etc.

EDIT2: I might have a huge amount of rows, so I want to avoid typing every single ID value where to replace mycolumn. Isn't it possible to match the ID values of 1 to 3 to the values 'a', 'b', 'c' in an automatic way, something like match(ID, ('a','b','c')) perhaps

I just want to replace all values of mycolumn by increasing order of ID. ID being strictly equivalent to what I call a row number in a matrix

EDIT3: I'd like a solution which would work in a general case with all sorts of values, not only the letters of the alphabet given here for simplicity. What if for example my values to replace in mycolumn are ('oefaihfoiashfe', 'fiaohoawdihoiwahopah', 'aefohdfaohdao')? However the ID row numbers will always be a sequence from 1 to N by 1.

Upvotes: 0

Views: 4128

Answers (4)

EJ Egyed
EJ Egyed

Reputation: 6084

You can try an update like the one below. This will update 1 > a, 2 > b, 3 > c, 4 > d, etc. When you reach ID 27, since there are no more letters, it will begin at a again and continue down the alphabet.

UPDATE mytable
   SET mycolumn = CASE MOD (id, 26)
                       WHEN 0 THEN 'z'
                       ELSE CHR (MOD (id, 26) + 96)
                  END;

Update

To update based on any list of values, you can try an update statement like the one below. If you add a 4th item to the comma delimited list, ID 4 in mytable will be set to whatever you specified as the 4th value.

UPDATE mytable
   SET mycolumn =
           (SELECT COLUMN_VALUE
              FROM (SELECT ROWNUM AS row_num, t.COLUMN_VALUE
                      FROM TABLE (
                               sys.odcivarchar2list ('oefaihfoiashfe',
                                                     'fiaohoawdihoiwahopah',
                                                     'aefohdfaohdao')) t)
             WHERE row_num = id);

Upvotes: 1

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1606

if you want the 3 rows to have different values a, b and c then you will have to write 3 update statements.

update mytable set mycolumn = 'a' where id = 1;
update mytable set mycolumn = 'b' where id = 2;
update mytable set mycolumn = 'c' where id = 3;

Upvotes: -1

user5683823
user5683823

Reputation:

Obviously, you should do this in a single update. Like this:

update mytable
set    mycolumn = case id when 1 then 'a' when 2 then 'b' when 3 then 'c' end
;

More compact (but also more cryptic, and only works in Oracle, while case expressions are in the SQL standard):

update mytable
set    mycolumn = decode(id, 1, 'a', 2, 'b', 3, 'c')
;

Note - this only works if there really are only three rows. If you have many more rows, make sure to add where id in (1, 2, 3) at the end. Otherwise all the OTHER values (in the other rows) will be updated to null!

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Hmmm . . . A row can only have one value. Perhaps something like this to assign random values:

update mytable
    set mycolumn = (case floor(dbms_random.random * 3)
                        case 0 then 'a' case 1 then 'b' else 'c'
                    end)

Upvotes: 0

Related Questions