?
Solved

updating column in table 1 by calculating using columns in table 2

Posted on 2013-01-08
3
Medium Priority
?
221 Views
Last Modified: 2013-01-08
Hi,

How can I do the following please?

I want to update a total column in 1 table using 2 columns in another table for multiple rows.

This is the rough query, except I get the error
'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'

eg

update tbPay
set total = (
SELECT      SUM(mPrice * iQty - ( (mPrice * iQty) * CONVERT(float, vCustom1)/100)) as CostIncDiscount
FROM         dbo.tbItem INNER JOIN
                      dbo.tbPurchase INNER JOIN
                      dbo.tbWholesalerP ON dbo.tbPurchase.iPurchaseId = dbo.tbWholesalerP.iPurchaseId INNER JOIN
                      dbo.tbPay ON dbo.tbPurchase.iPurchaseId = dbo.tbPay.iPurchaseId ON dbo.dbo.tbItem.iPurchaseId = dbo.tbPurchase.iPurchaseId
WHERE  (dbo.dbo.tbItem.iProductId BETWEEN 1 AND 4) AND
                      (dbo.dbo.tbItem.dtCancel IS NULL) AND   dbo.tbWholesalerP.dtDispatch > '01 dec 2012'
GROUP BY dbo.dbo.tbItem.iPurchaseId)
FROM         dbo.tbItem INNER JOIN
                      dbo.tbPurchase INNER JOIN
                      dbo.tbWholesalerP ON dbo.tbPurchase.iPurchaseId = dbo.tbWholesalerP.iPurchaseId INNER JOIN
                      dbo.tbPay ON dbo.tbPurchase.iPurchaseId = dbo.tbPay.iPurchaseId ON dbo.dbo.tbItem.iPurchaseId = dbo.tbPurchase.iPurchaseId
WHERE (dbo.dbo.tbItem.iProductId BETWEEN 1 AND 4) AND
                      (dbo.dbo.tbItem.dtCancel IS NULL) AND   dbo.tbWholesalerP.dtDispatch > '01 dec 2012'
0
Comment
Question by:louise_8
3 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38757353
try this

update tbPay
set total = A.CostIncDiscount
FROM         dbo.tbItem INNER JOIN
                      dbo.tbPurchase INNER JOIN
                      dbo.tbWholesalerP ON dbo.tbPurchase.iPurchaseId = dbo.tbWholesalerP.iPurchaseId INNER JOIN
                      dbo.tbPay ON dbo.tbPurchase.iPurchaseId = dbo.tbPay.iPurchaseId ON dbo.dbo.tbItem.iPurchaseId = dbo.tbPurchase.iPurchaseId
Inner Join (
SELECT      tbItem.iPurchaseId, SUM(mPrice * iQty - ( (mPrice * iQty) * CONVERT(float, vCustom1)/100)) as CostIncDiscount
FROM         dbo.tbItem INNER JOIN
                      dbo.tbPurchase INNER JOIN
                      dbo.tbWholesalerP ON dbo.tbPurchase.iPurchaseId = dbo.tbWholesalerP.iPurchaseId INNER JOIN
                      dbo.tbPay ON dbo.tbPurchase.iPurchaseId = dbo.tbPay.iPurchaseId ON dbo.dbo.tbItem.iPurchaseId = dbo.tbPurchase.iPurchaseId
WHERE  (dbo.dbo.tbItem.iProductId BETWEEN 1 AND 4) AND 
                      (dbo.dbo.tbItem.dtCancel IS NULL) AND   dbo.tbWholesalerP.dtDispatch > '01 dec 2012'
GROUP BY dbo.dbo.tbItem.iPurchaseId) A on A.iPurchaseId =  tbPay.iPurchaseId 
WHERE (dbo.dbo.tbItem.iProductId BETWEEN 1 AND 4) AND 
                      (dbo.dbo.tbItem.dtCancel IS NULL) AND   dbo.tbWholesalerP.dtDispatch > '01 dec 2012' 

Open in new window


Take backup of the existing data before executing the update statement.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 600 total points
ID: 38757370
Something like this perhaps:
UPDATE  tbPay
SET     total = x.CostIncDiscount
FROM    dbo.tbPay y
        INNER JOIN (SELECT  i.iPurchaseId,
                            SUM(mPrice * iQty - ((mPrice * iQty) * CONVERT(float, vCustom1) / 100)) AS CostIncDiscount
                    FROM    dbo.tbItem i
                            INNER JOIN dbo.tbPurchase p ON i.iPurchaseId = p.iPurchaseId
                            INNER JOIN dbo.tbWholesalerP w ON i.iPurchaseId = w.iPurchaseId
                    WHERE   i.iProductId BETWEEN 1 AND 4
                            AND i.dtCancel IS NULL
                            AND w.dtDispatch > '01 dec 2012'
                    GROUP BY i.iPurchaseId
                   ) x ON y.iPurchaseId = x.iPurchaseId 

Open in new window

0
 

Author Closing Comment

by:louise_8
ID: 38757484
Thanks to both comments
acperkins, this worked perfect and was simple. Cheers
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question