Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.