Insert Max Value of Column into Another Column

Question

I have a table defined by:

create table apple(

A number,

B number);

Now, I need to get values in the table such as the following:

A      B
------------------
1      4(max of A)
2      4(max of A)
3      4(max of A)
4      4(max of A)

How can I insert these rows, making B the maximum value of A?

Answer 1

Welp, first you want to insert 1-4 into your table:

insert into apple (a) values (1)
insert into apple (a) values (2)
insert into apple (a) values (3)
insert into apple (a) values (4)

Next, you're going to want to update your table to set b:

update apple set b = (select max(a) from apple)

As you can see, it's a two-part process. You can't get the max of a until you've created that column!

Answer 2

And of course, if you're wanting to have a select statement to grab that other field, use the OVER clause:

SELECT a, MAX(a) OVER() as b
FROM table;

Edited:

And for an existing table you can do:

UPDATE t SET b = maxcnt
FROM (
    SELECT *, MAX(a) OVER() as maxcnt
    FROM table
   ) t;

(I think this works in Oracle... definitely fine in MS-SQL)

Rob

Answer 3

Since 11g version you are able to use Virtual colums (their values calculated in real-time) So that you should change your column definition as follows:

create table apple (
A number,
 B number GENERATED ALWAYS AS ( max(A) ) VIRTUAL 
);

I dont have Oracle 11g for testing, so cant check, but it should be working. Also you could use user-defined function for Virtual column.

See http://www.oracle-base.com/articles/11g/VirtualColumns%5F11gR1.php for more examples and info!

Official docs for Create table in 11g: http://download.oracle.com/docs/cd/B28359%5F01/server.111/b28286/statements%5F7002.htm