Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-06-2009
09:07 AM

08-06-2009
09:07 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-06-2009
01:20 PM

08-06-2009
01:20 PM

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

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