Update all rows except one row in a table

Database
Enthusiast

Update all rows except one row in a table

I need to update all rows except one row in a table. Example

A B C 1 2 3 U
A B C 1 2 4 U
X Y Z 6 7 8 U
T U I P U 1 U
A K T 1 2 4 U
X Y Z J 7 8 U
T R I P U 1 U

This data needs to be changed to

A B C 1 2 3 U
A B C 1 2 4 I
X Y Z 6 7 8 I
T U I P U 1 I
A K T 1 2 4 I
X Y Z J 7 8 I
T R I P U 1 I

Now what I am doing is selecting the top 1 row into a virtual table doing a left outer join with the same table to pull out all the null values. But I feel there should be a better way to do this. Can you any one of you guide me to come up with a better solution. I can not use row number or rank as it would give similar rank for to distinct rows.

Your help is highly appreciarted

1 REPLY

Re: Update all rows except one row in a table

Hi,

To accomplish this, you could try:

CREATE SET TABLE sup_dmcatee.atable ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
a int,
b int
)
UNIQUE PRIMARY INDEX ( a,b );

insert into sup_dmcatee.atable
values (1,2);

insert into sup_dmcatee.atable
values (3,4);

insert into sup_dmcatee.atable
values (5,6);

insert into sup_dmcatee.atable
values (7,8);

UPDATE sup_dmcatee.atable
SET b = 9
WHERE (a,b) NOT IN (select a,b from sup_dmcatee.atable where a= 1 and b=2);

select * from sup_dmcatee.atable