CODEWITHSUNDEEP
sqloracle-database
Zed
Zed

Reputation: 19

How to put the greatest value from others columns in another column - oracle sql

I'm working with Oracle SQL. i need to stock the greatest value of multiple columns, in a new total column. I know that i can do it with function "GREATEST", but when i execute my UPDATE, i get errors like 'the request get multiple row' and even if i add "rownum <= 1", i get nothing !

What i have to do on my update? Thank you !!

col_1 col_2 col_3 total
5 0 4 5
3 1 1 3

and this is my request (that not working)

    UPDATE my_table set total= 
(
select greatest(col_1, col_2, col_3) from mytable
);

Upvotes: 0

Views: 171

Answers (2)

Ashwik
Ashwik

Reputation: 61

We can achieve the exact above requirement:

CREATE TABLE my_table   
(col_1 NUMBER,
col_2     NUMBER,
col_3 NUMBER);

INSERT INTO my_table (col_1,col_2,col_3) VALUES(5,0,4);
INSERT INTO my_table (col_1,col_2,col_3) VALUES(3,1,1);
COMMIT;


ALTER TABLE  my_table  ADD total  NUMBER;

UPDATE my_table  SET total=greatest(col_1, col_2, col_3);
--2 rows updated. 
COMMIT;

SELECT * FROM my_table ;

Result

Upvotes: 1

MT0
MT0

Reputation: 168361

Don't use a sub-query:

UPDATE my_table
SET total = GREATEST(col_1, col_2, col_3);

Then, for the sample data:

CREATE TABLE my_table (col_1, col_2, col_3, total) AS
SELECT 5, 0, 4, -1 FROM DUAL UNION ALL
SELECT 3, 1, 1, -1 FROM DUAL;

After updating, then:

SELECT * FROM my_table;

Outputs:

COL_1 COL_2 COL_3 TOTAL
5 0 4 5
3 1 1 3

If you really want to use a sub-query (I don't see why you would) then you can correlate on the ROWID pseudo-column:

UPDATE my_table t
SET total = ( SELECT GREATEST(col_1, col_2, col_3)
              FROM   my_table m
              WHERE  t.ROWID = m.ROWID );

sqlfiddle here

Upvotes: 1

Related Questions