Prev: Sum on Rounded column
Next: Change RDL file datsource
From: Jami on 29 Jun 2010 04:50


Dear all

sql 2000 question

i have two table



create table tab1 (tno varchar(10), id_val varchar(20), ref char(2))
create table tab2 (tno varchar(10), id_val varchar(20))

in tab1 tno have multiple id_vals

whereas in tab2 i have all tno values

i want to update tab2 with max id_val against the tno in tab1 where ref
in ('R1','R2','R4')

insert into tab1 values ('1','123','R1')
insert into tab1 values ('1','423',null)
insert into tab1 values ('2','323','R4')
insert into tab1 values ('2','423',null)
insert into tab1 values ('3','323','R3')
insert into tab1 values ('3','423',null)
insert into tab1 values ('3','223','S1')

insert into tab2 values ('1',null)
insert into tab2 values ('2',null)
insert into tab2 values ('3',null)

what will be the query for achieving this task

Regards

Jami





*** Sent via Developersdex http://www.developersdex.com ***
From: Baiju K U on 29 Jun 2010 05:02
Is this what you want ?

UPDATE tab2
SET id_VAL =
(Select max(Id_Val) From tab1 Where tab1.Tno = tab2.Tno and
tab1.Ref in ( 'R1','R4','R3'))


"Jami" <jami.khan(a)yahoo.com> wrote in message
news:%235LIjg2FLHA.2276(a)TK2MSFTNGP06.phx.gbl...
>
>
> Dear all
>
> sql 2000 question
>
> i have two table
>
>
>
> create table tab1 (tno varchar(10), id_val varchar(20), ref char(2))
> create table tab2 (tno varchar(10), id_val varchar(20))
>
> in tab1 tno have multiple id_vals
>
> whereas in tab2 i have all tno values
>
> i want to update tab2 with max id_val against the tno in tab1 where ref
> in ('R1','R2','R4')
>
> insert into tab1 values ('1','123','R1')
> insert into tab1 values ('1','423',null)
> insert into tab1 values ('2','323','R4')
> insert into tab1 values ('2','423',null)
> insert into tab1 values ('3','323','R3')
> insert into tab1 values ('3','423',null)
> insert into tab1 values ('3','223','S1')
>
> insert into tab2 values ('1',null)
> insert into tab2 values ('2',null)
> insert into tab2 values ('3',null)
>
> what will be the query for achieving this task
>
> Regards
>
> Jami
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***


From: Jami on 30 Jun 2010 03:49

Thanx


*** Sent via Developersdex http://www.developersdex.com ***
 | 
Pages: 1
Prev: Sum on Rounded column
Next: Change RDL file datsource