PLS. HELP. SQL NEWBIE

hello all. please tell me why the following update staement doesn't
work.

what i want to do is update tblmaster.mcol2 based on the value of
tblheader.hcol2

hcol2 values:
1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2

i tried it using query analyzer but it still adds even though the
value of hcol2 is 2.

create table tblmaster ( mcol1 nvarchar(3),
mcol2 float )

insert into tblmaster values ('001', 1)
insert into tblmaster values ('002', 1)
insert into tblmaster values ('003', 1)
insert into tblmaster values ('004', 1)
insert into tblmaster values ('005', 1)

create table tblheader ( hcol1 int,
hcol2 smallint )

create table tbldetails ( dcol1 int,
dcol2 nvarchar(3),
dcol3 float,
dcol4 float )

insert into tblheader values (1, 1)
insert into tblheader values (2, 1)
insert into tblheader values (3, 2)

insert into tbldetails values ( 1, '001', 1, 10 )
insert into tbldetails values ( 1, '002', 1, 10 )

insert into tbldetails values ( 2, '001', 1, 10 )
insert into tbldetails values ( 2, '003', 2, 10 )

insert into tbldetails values ( 3, '004', 1, 10 )
insert into tbldetails values ( 3, '005', 2, 10 )

declare @.lo as int
declare @.hi as int

set @.lo = 1
set @.hi = 3

UPDATE tblmaster
SETmcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t, tblheader h, tbldetails d
WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi

select * from tblmaster

TIA,

diegoHi Rey

It is not clear how your tables are actually related but you may want to try
something like:

"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego|||Hi Rey

It is not clear how your tables are actually related but you may want to try
something like:

UPDATE t
SET mcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t
JOIN tbldetails d ON t.mcol1 = d.dcol2
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi

As tblmaster has is a one to many relationship with tbldetails you will have
to either restrict which rows match or use an aggregate

UPDATE t
SET mcol2 = mcol2 + val
FROM tblmaster t
JOIN ( SELECT d.dcol2, SUM( CASE WHEN h.col2 = 1 THEN d.dcol3 * dcol4
ELSE -1 * dcol3 * dcol4 END ) as val
FROM tbldetails d
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi GROUP BY d.dcol2 ) d ON t.mcol1 =
d.dcol2

John

"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego