Update Statement

Database
Enthusiast

Update Statement

Hi Friends,

Caould you please help me with an update statement query?

I have the data in the table as below:

Territory   Sysid

a                123

b                 345

c                 -1

d                 -1

e                -1

Now i want update all the -1 Sysid sequentially. i.e need to update -1 records from max(SYSID) in the table + 1. So we would be updating -1 records as 346, 347, 348.

Could you please help??

Regards

Sunny

1 REPLY
Teradata Employee

Re: Update Statement

I think this is what you want

drop table StartingTable;
create multiset volatile table StartingTable
(
col1 char(1),
col2 integer
)unique primary index( col1 )
on commit preserve rows;

insert into StartingTable values('a', 123);
insert into StartingTable values('b', 345);
insert into StartingTable values('c', -1);
insert into StartingTable values('d', -1);
insert into StartingTable values('e', -1 );

create multiset volatile table WKTable
as(
select
BASE.col1,
MXCL.maximo + row_number() over(order by BASE.col1) as NewCol2
from StartingTable BASE
cross join( select max(col2) as maximo from StartingTable )as MXCL
where BASE.col2=-1
)with data
unique primary index( col1 )
on commit preserve rows;

update StartingTable ST
set col2 = WKTable.NewCol2
where ST.col1 = WKTable.Col1
;