Help with Update Query - 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 2008 Forums

Transact-SQL (2008)

Help with Update Query

Author

Topic

robbie_62
Starting Member

1 Post

Posted - 2009-11-23 : 16:10:55

Hi Guys

I have the following 2 tables :

Table A Fields

Orderid (int)
total_qty (smallint)
total_deductions (decimal(18,2))

Table B Fields

TransDate (Date)
Orderid (int)
single_deductions (decimal(18,2))
deduction_remaining (decimal(18,2))

Sample Data :

Table A

Orderid Total_Qty Total_Deduction
1 2 60

Table B

Transdate Orderid Single_deductions Deduction_Remaining
17-09-2009 1 45 0
17-09-2009 1 5 0

What I am trying to do is the following :

1)
Using a query to calculate the sum of single_deductions field for each orderid in Table B

2)subtract this total from the total_deduction field in Table A

3)Divide the result by the Total_Qty field in Table A

4)Update the deduction_remaining field in Table B to this result

This will give me the following results :

Part 1 = 45+5 = 50
Part 2 = 60-50 = 10
Part 3 = 10/2 = 5
Part 4 = Update deduction_remaining to 5

Hope this makes sense,I have only included the fields from both tables that are relevant,

Below is the query I have so far

(Select (tablea.total_deduction - sum(tableB.single_deduction))/tablea.total_qty as Deduction_remaining from TableB inner join TableA on TableB.orderid=TableA.orderid
where TableB.transdate between '2009-11-17' and '2009-11-17' GROUP BY TableA.orderid,TableB.total_deduction,TableB.total_qty )

This gives me the adjustment amount for each orderid but I need to apply this to an update statement.

If I try to use this query in an update statement it tells me that the "Subquery returned more than 1 Value"

I can see this is because the above query gives me all records,

Would appreciate any suggestions,

Rob

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-02 : 12:00:58

Here's one option...


--structure
declare @TableA table (
    Orderid int,
    total_qty smallint,
    total_deduction decimal(18,2))

declare @TableB table (
    TransDate datetime,
    Orderid int,
    single_deductions decimal(18,2),
    deduction_remaining decimal(18,2))
--/

--data
insert @TableA (Orderid, Total_Qty, Total_Deduction)
          select 1, 2, 60
union all select 2, 3, 100

insert @TableB (Transdate, Orderid, Single_deductions, Deduction_Remaining)
          select '20090917', 1, 45, 0
union all select '20090917', 1, 5, 0
union all select '20090917', 2, 11, 0
--/

--calculation
; with
  t1 as (select Orderid, sum(single_deductions) as deductions from @TableB group by Orderid)
, t2 as (select b.*, (a.total_deduction - b.deductions) / a.Total_Qty as New_Deduction_Remaining
         from @TableA a inner join t1 b on a.Orderid = b.Orderid)
, t3 as (select a.*, New_Deduction_Remaining from @TableB a inner join t2 b on a.Orderid = b.Orderid)
update t3 set Deduction_Remaining = New_Deduction_Remaining
--/

--output
select * from @TableB
/*
TransDate   Orderid  single_deductions  deduction_remaining
----------- -------- ------------------ --------------------
2009-09-17  1        45.00              5.00
2009-09-17  1        5.00               5.00
2009-09-17  2        11.00              29.67
*/
--/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources