Solved

MSSQL Query help

Posted on 2013-12-29
4
346 Views
Last Modified: 2013-12-30
Hello there,

I have this 2 tables SupplierOrder and SupplierOrderDetails which are linked by SupplierOrder PK. Now  I have this col called isComplete in the SupplierOrder table which I want o update to true once all the values in the SupplierORderDetails table's isComplete are all true for that supplierOrder ID.see the attachment for the tables. I have tried myseolf with this query but I think it could be a better way or more efficient.

SELECT 1 
        FROM supplierOrder so
        inner JOIN supplierOrderdetails sod 
          ON so.id = sod.supplierOrderID
       WHERE so.id = 1
         AND sod.isComplete= 1

Open in new window

12-30-2013-8-19-54-AM.gif
0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 39745611
Can you try with this update query to update all the records in the table based upon the statement you have provided.

UPDATE supplierOrder
SET isComplete= 1
WHERE id IN (
SELECT supplierOrderID
FROM supplierOrderdetails
WHERE COUNT(*) = SUM(case when iscomplete = 1 THEN 1 else 0 end)
GROUP BY supplierOrderID)
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39745634
For a one-time fix the query below should work.  For ongoing updates I would recommend a trigger.

UPDATE SupplierOrder
SET IsComplete = CASE WHEN SOD.CompleteCount < SOD.ItemCount THEN 0 ELSE 1 END
FROM SupplierOrder AS SO
INNER JOIN
   (
      SELECT SupplierOrderID, COUNT(1) AS ItemCount, SUM(IsComplete) AS CompleteCount
      FROM SupplierOrderDetails
      GROUP BY SupplierOrderID
   ) AS SOD
   ON SO.ID = SOD.SupplierOrderID
0
 

Author Closing Comment

by:zolf
ID: 39745640
thanks!!
0
 
LVL 4

Expert Comment

by:ItWorked
ID: 39745695
Is this what you were searching for?
Create table #SaleOrder
(
	SaleID int,
	IsCompleted bit
)
Create table #SaleOrderDetails
(
	SaleDetailsID int,
	SaleOrderID int,
	IsCompleted bit
)
insert into #SaleOrder values(1,0)
insert into #SaleOrder values(2,0)

insert into #SaleOrderDetails values(1,1,1)
insert into #SaleOrderDetails values(2,1,1)

insert into #SaleOrderDetails values(3,2,1)
insert into #SaleOrderDetails values(4,2,1)
insert into #SaleOrderDetails values(5,2,0)


Select * from #SaleOrder
Select * from #SaleOrderDetails


update OSO
set OSO.IsCompleted=1
from #SaleOrder as OSO
inner join (
				Select so.SaleID
				from #SaleOrder as so
				inner join #SaleOrderDetails sod on so.SaleID=sod.SaleOrderID
				group by so.SaleID
				having count(so.SaleID)=count(case when sod.iscompleted=1 then so.SaleID
                                            else null end)
           )  t on OSO.SaleID=t.SaleID






Select * from #SaleOrder
Select * from #SaleOrderDetails

drop table #SaleOrder
drop table #SaleOrderDetails

Open in new window

0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

617 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