Updating a field based on calculation from two tables

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)

Updating a field based on calculation from two tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-13 : 08:52:12

Adil Akram writes "Details for what to do
=======================

I have two tables SQL Server 2000 named "Received" and "Return" with following fields.

Receive
======
JobNo * (Primary Key)
PartNo
Status (i.e. Job completion status. Completed or Not Completed. True/False)
Quantity

Return
=====
JobNo * (Foreign Key)
ReturnDate *
Quantity

Receive table stores parts received to work on and a new job No. assigned to each received consignment.

And Return table stores the parts returned after working on them. Parts for a single job can be returned in multiple days that is why "ReturnDate" is added to make a composite primary key. But more than one consignment can not be returned for a single job in a day.

Now I want is that whenever the quantity of returned parts for a particular job becomes equal to receive the "Status" should be set to True.

I have written the following query to calculate balance parts remaining to be returned and it works absolutely right.

(SELECT receive.quantity-sum(return.quantity) AS bal FROM receive LEFT JOIN return ON receive.jobNo=return.jobNo WHERE receive.job='3657' GROUP BY parts.qtyreceived)

Now to automatically update the "Status" field I have extended the query as:

UPDATE receive SET status=(SELECT receive.quantity-sum(return.quantity) AS bal FROM receive LEFT JOIN return ON receive.jobNo=return.jobNo WHERE receive.job='3657' GROUP BY parts.qtyreceived) WHERE return.jobNo='3657'

Please help me to write this query. I am very thank full to you.

regards,
Adil"

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-13 : 09:30:07

Use a case statement in the query. I.E.

UPDATE receive
SET status=(SELECT case when (receive.quantity-sum(return.quantity))=0 then 1 else 0 end AS bal FROM receive LEFT JOIN return ON receive.jobNo=return.jobNo WHERE receive.job='3657' GROUP BY parts.qtyreceived) WHERE return.jobNo='3657'

1=true, 0=false if you are using the bit datatype which is SQL servers equivalent of boolean.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources