pvyas

Dear all,

I have a table T with numeric fields A and B. Now I want to update a
column in the same table according to the following logic :

[Before]

A B
70 NULL
90 NULL
100 NULL
80 NULL
... ..

I want it to look like this

A B
100 1
90 2
80 3
... ..

I tried all the methods discussed in this forum (including Dr.
Rozenshtein) but they all assume that the fields are already ordered
and we just need to attach the "row numbers" . I have really tried but
seem to get nowhere.

Any help is appreciated. Thanks in advance.

Warm regards,
Puneet Vyas

idogan_tech

Assuming column A is unique:
SELECT a, b= IDENTITY(10)
INTO #tmprownum
FROM t
ORDER BY a DESC

UPDATE t
FROM t, #tmprownum r
SET b = r.b
WHERE t.a=r.a

i.d.

ggallagher

From the values you've posted, it looks like you're trying to produce
a result where column "B" represents the "ranking" of values in column
"A" (highest = 1, next = 2, etc.). This can be done by creating a new
table (hint: tempdb is a good place for this) using "select into"
selecting all the "A" values from the original table, specifying the
"ranking" column as having the identity property, with an order by
clause to produce the results in the proper sort order. You may then
use the newly minted table to update or repopulate the original table,
as desired. Details on the syntax required to do this are in the
on-line manuals at http://manuals.sybase.com/onlinebooks/group-as/asg1250e/
(12.5 manuals).


HTH,

Guy

larry coon

(snipped)

First, let me say that if you want to STORE data like you want
to store in column B, you probably have a bad design or are
taking the wrong approach to a solution. If B is derivable,
you don't need to store it. Worse, if you insert another row
in the table and somehow fail to re-update column B, your data
in column B is now wrong.

That being said.....

-- Create the table and insert rows
create table x (a int, b int)

insert into x values (70, null)
insert into x values (90, null)
insert into x values (100, null)
insert into x values (80, null)

-- Here's the table & data
select * from x

a b
----------- -----------
70 NULL
90 NULL
100 NULL
80 NULL

-- First a query to show the result you want
select a,
( select 1 + count(*)
from x x2
where x1.a < x2.a
)
from x x1
order by 2

-- The result of the above query
a
----------- -----------
100 1
90 2
80 3
70 4

-- Now an update to actually change the data
update x
set b = ( select 1 + count(*)
from x x2
where x1.a < x2.a
)
from x x1

-- Here's the updated table
select * from x order by b

a b
----------- -----------
100 1
90 2
80 3
70 4


Larry Coon
University of California

pvyas

Oops .. i forgot to mention that ... column A is unfortunately
non-unique. Moreover , it might be null too.

Also , on a separate note , i find that using the identity column
approach is only allowed in the "select into" statement. Why is such
an approach not supported by databases for a "insert - select"
combination ?

pvyas

This is a great solution. But the only hitch is that if "a" has
repeating values the counter goes for a toss. However , I love how the
inner query is structured. The solutions is go good that I want to
change the problem to fit it But, alas, I am still stuck where I
was.

idogan_tech

OK. but what value you wanna update B to when A is NULL ?

in case of duplicate values of A, my solution would be:

SELECT DISTINCT(a), b= IDENTITY(10)
INTO #tmprownum
FROM t
WHERE a != NULL
ORDER BY a DESC

UPDATE t
FROM t, #tmprownum r
SET b = r.b
WHERE t.a=r.a i.d.

In Insert-Select, you already have a table so you can't create any
column INSERT is not DDL unlike SELECT-INTO.

joe.celko

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Is this what you meant??

CREATE TABLE Foobar -- not a table because it has no key!
(a INTEGER,
b INTEGER);

Then use:

UPDATE Foobar
SET b
=(SELECT COUNT(*)
FROM Foobar AS F1
WHERE F1.a <= Foobar.a);

or, depending on your rules for ties, use

UPDATE Foobar
SET b
=(SELECT COUNT(DISTINCT a)
FROM Foobar AS F1
WHERE F1.a <= Foobar);

or, depending on your rules for NULLS, use

UPDATE Foobar
SET b
=(SELECT COUNT(a)
FROM Foobar AS F1
WHERE F1.a <= Foobar.a);

Now do you see why we want DDL and clear specs?