CODEWITHSUNDEEP
sqloracle-databaseplsql
prabin
prabin

Reputation:

Insert Max Value of Column into Another Column

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?

Upvotes: 2

Views: 4153

Answers (3)

zmische
zmische

Reputation: 849

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_11gR1.php for more examples and info!

Official docs for Create table in 11g: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

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

Upvotes: 1

Eric
Eric

Reputation: 95153

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!

Upvotes: 5

Related Questions