Re: How to add and populate a new column?

From: Zev Sero <zsero_at_mail.idt.net>
Date: 1996/12/10
Message-ID: <32ada75f.202813803@news.idt.net>#1/1

On Tue, 10 Dec 1996 15:37:34 GMT, zlm101_at_psu.edu (Z. Martinez) wrote:

>I need to add a new column, called rank, in a table called customers.
>The "rank" column depends on another column called quantity.
>So, the row that has the highest "quantity" value gets a "rank" value
>of 1, and the lowest quantity value gets the last rank.

alter table customers

    add (rank number(10)); -- or whatever size declare

    seq number := 0;
begin

    for rec in (

	select rowid as line from customers
	    order by quantity desc
    ) loop
	seq := seq + 1;
	update customers
	    set rank = seq
	    where rowid = rec.line
	;

    end loop;
end;
/
alter table customers

    modify (rank not null); -- if you want to

---

Look up the SQL manual for the exact syntax of the alter table statements, since I'm working from memory here.

--
Zev Sero		Don't blame me, I voted for Harry Browne
zsero_at_mail.idt.net
zsero_at_technimetrics.com

Received on Tue Dec 10 1996 - 00:00:00 CST