Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-23 : 08:19:43

Mala writes "Hi,

I faced a small probelm with simple update statement. Is my Sp
I have something like following satement.

select id,month1,month2,month3month4...month12,month12 as Total
into #tmp from table
[here all month1 to 12 are subqueries]

update #tmp set total=month1+month2+...month12

But this doesn't give accurate value. Though I can add all subquery result in the main query itself, i don't want to do that as it is time consuming. But if I update it as below I will be produce accurate value.

update a
set a.total=b.month1+b.month2...+b.month12
from #tmp a,#tmp b
where a.id=b.id

Why is it so ? Please help.

Thanks in advance.

Bye,
Mala"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-24 : 09:45:29

Post your query's maybe there's a syntax error somewhere.

malamohan
Starting Member

34 Posts

Posted - 2002-10-24 : 13:33:49

Hi,

My query goes like this for the passsing parameters for SP.

select a.id,isnull(select sum(amt) from table1 where month='01'
and a.id=id and a.code=code),0) as Month1,
isnull(select sum(amt) from table1 where month='02'
and a.id=id and a.code=code),0) as Month2,
...
...
isnull(select sum(amt) from table1 where month='12'
and a.id=id and a.code=code),0) as Month12,
sum(a.amt) as total
into #tmp_total
from table1 a
where id=@v_id and code=@v_code

update #tmp_total set total=month1+month2+.........mont12

The above update doesn't assing accurate value to Total column.

Where as if I update it as belwo it will assign proper value

update a
set a.total=b.month1+month2+...month12
from #tmp_total a,Tmp_total b
where a.id=b.id

Can you tell me what could be the problem ?

Bye,
Mala

mala

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-24 : 15:55:27

Sounds like you need a GROUP BY

Something like this:

 
SELECT sum(amt) 
FROM table1 
WHERE id=@v_id and code=@v_code 
GROUP BY month

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

malamohan
Starting Member

34 Posts

Posted - 2002-10-25 : 13:47:52

Hi,

Why it required ggroup by ? Is it not a simple update statement ?

I mean somethint like this.

Create table #tmp
(id cahr(1),
a int,
b int,
c int)

[Assuming I don't want to insert into c right now]
insert into #tmp(id,a,b,) values ('a',10,20)
insert into #tmp(id,a,b,) values ('b',30,20)
update #tmp set c=a+b
This doesn't work ? I am confused.

Bye,
Mala

mala

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-25 : 13:51:51

malamohan,
From what I can tell, it sounds like you are trying to get totals per month, right? I think the whole temp table/update this is not the way to go. I think the way I was trying to show you will give you what you want.

The problem is I don't know exactly what you want. Could you post some sample data, and some sample results and we can help you make a query that will give you the sample results.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

malamohan
Starting Member

34 Posts

Posted - 2002-10-27 : 10:39:39

Hi Michael ,

The table is something like this.

create table Table1
(Id varchar(10),
@v_Date2 @v_Date2time,
Amount float,
Type varchar(2))

insert into table1 values('I001','2002-09-30',1000,'NEW')
insert into table1 values('I002','2002-09-30',2000,'NEW')
insert into table1 values('I003','2002-09-28',1000,'OLD')
insert into table1 values('I003','2002-03-28',1000,'REN')
insert into table1 values('I004','2002-09-28',1000,'REN')
insert into table1 values('I002','2002-09-28',1000,'OLD')
insert into table1 values('I003','2002-03-30',1000,'REN')
insert into table1 values('I003','2002-09-30',2000,'OLD')
insert into table1 values('I003','2002-09-30',5000,'REN')

[just a sample @v_Date2]

SO out of this I have to produce result some thing like this.

select a.id,sum(a.amount) as Op_Bal,
isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='NEW'),0) as ON_Bal,
isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='OLD'),0) as OD_Bal,
isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='REN'),0) as OR_Bal,
sum(isnull(a.amount,0))+
isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='NEW'),0)
- isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='OLD'),0)
- isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='REN'),0)
as Cl_bal

from table1 a
where date < '2002-09-29'
group by id

Though I am getting accurate result out of above query, I don't want lock to talbe
with so much subsueries [it was taking longer time due to huge data]
I ended up changing my satement as below.

select a.id,sum(a.amount) as Op_Bal,
isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='NEW'),0) as ON_Bal,
isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='OLD'),0) as OD_Bal,
isnull((select sum(amount) from table1
where id=a.id and date between '2002-09-29' and '2002-10-01' and type='REN'),0) as OR_Bal
into #tmp
from table1 a
where date < '2002-09-29'
group by id

select * from #tmp

alter table #tmp
add CL_bal float

update #tmp set cl_Bal=OP_BAL+ON_BAL-OR_bAL-OD_bAL

[YES with the above sample data result is OK]

But value of Cl_bal was not accurate. So I used self join to update it as below
to get accurate value.

update a
set a.cl_bal=b.OP_BAL+b.ON_BAL-b.OR_bAL-b.OD_bAL
from table1 a,table1 b
where a.id=b.id

Uh this worked. Why ? What was wrong with my simple stright forward update ?
When my team leader asked me why I went for self join to update where as
I could have done with simple update [also asked me whether is it a bug in SQL ?]
I couldn't answer. I am still confused.

Please let me know is there any problem in my statement ?

Bye,
Mala

mala

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-10-27 : 15:35:45

Use a CASE expression instead of subqueries.

SELECT my_id, SUM(amount)AS op_bal,
SUM(CASE WHEN my_date BETWEEN '2002-09-29'
AND'2002-10-01'
AND type = 'NEW')
THEN amount ELSE 0.00 END) AS on_bal,

...
FROM Table1 AS T1
WHERE my_date < '2002-09-29'
GROUP BY my_id;

--CELKO--
Joe Celko, SQL Guru

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-28 : 07:57:05

I see that you have used a float datatype.

quote:

The table is something like this.
create table Table1
(Id varchar(10),
@v_Date2 @v_Date2time,
Amount float,
Type varchar(2))

A 'float' is an approximate datatype. The value returned by SQL server for a float is always approximate. This is the nature of float datatypes, so you should not be using float values in cases where accuracy is important (use something like 'money' instead)

That would probably explain why you were inaccurate values when adding up directly. Though it beats me as to why you didnt have the same problem with the self join.

quote:

Uh this worked. Why ? What was wrong with my simple stright forward update ?
When my team leader asked me why I went for self join to update where as
I could have done with simple update [also asked me whether is it a bug in SQL ?]
I couldn't answer. I am still confused.

-ashok
http://www.unganisha.org

malamohan
Starting Member

34 Posts

Posted - 2002-10-28 : 13:04:01

Hi,

I don't think case statment giving accurate result. Yes even I am amazed to see the result was perfect when I used self join.

Bye,
Mala

mala

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-29 : 18:00:03

DROP TABLE Table1
CREATE TABLE Table1
(
ID VARCHAR(10),
VDate DATETIME,
Amount MONEY,
Type VARCHAR(3)
)

insert into table1 values('I001','2002-09-30',1000,'NEW')
insert into table1 values('I002','2002-09-30',2000,'NEW')
insert into table1 values('I003','2002-09-28',1000,'OLD')
insert into table1 values('I003','2002-03-28',1000,'REN')
insert into table1 values('I004','2002-09-28',1000,'REN')
insert into table1 values('I002','2002-09-28',1000,'OLD')
insert into table1 values('I003','2002-03-30',1000,'REN')
insert into table1 values('I003','2002-09-30',2000,'OLD')
insert into table1 values('I003','2002-09-30',5000,'REN')

SELECT
AA.[ID],
ISNULL(AA.OP_Bal,0) As OP_Bal,
ISNULL(BB.ON_Bal,0) As ON_Bal,
ISNULL(BB.OD_Bal,0) As OD_Bal,
ISNULL(BB.OR_Bal,0) As OR_Bal,
ISNULL(AA.OP_Bal,0) + ISNULL(BB.ON_Bal,0) - ISNULL(BB.OR_Bal,0) - ISNULL(BB.OD_Bal,0) As CL_Bal
FROM
(
SELECT A.[ID], SUM(A.Amount) As OP_Bal
FROM
TABLE1 As A
WHERE A.VDate < '2002-09-29'
GROUP BY A.[ID]
)
AA LEFT JOIN
(
SELECT B.[ID],
SUM(
CASE WHEN B.Type = 'NEW' THEN B.Amount
ELSE 0.00
END
) As ON_Bal,
SUM(
CASE WHEN B.Type = 'OLD' THEN B.Amount
ELSE 0.00
END
) As OD_Bal,
SUM(
CASE WHEN B.Type = 'REN' THEN B.Amount
ELSE 0.00
END
) As OR_Bal
FROM
TABLE1 As B
WHERE B.VDate Between '2002-09-29' And '2002-10-01'
GROUP BY B.[ID]
)
BB ON AA.[ID] = BB.[ID]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources