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 2012 Forums
 Transact-SQL (2012)
 Update lines with subquery

Author  Topic 

benking9987
Posting Yak Master

124 Posts

Posted - 2013-11-29 : 14:29:23
Hello:

I have a little update sequence I need to do and I think it's a little outside of my understanding.

I have data that looks like this:

ordernumber item location stagecomplete
order1 item1 location1
order1 item2 location1
order2 item1 location2
order2 item2 location2
order2 item3 NULL
order2 item4 NULL
order3 item1 location3
order3 item2 location3
order3 item3 location3


I want the update statement to update the "stagecomplete" column such that it looks like this:

ordernumber item location stagecomplete
order1 item1 location1 1
order1 item2 location1 1
order2 item1 location2
order2 item2 location2
order2 item3 NULL
order2 item4 NULL
order3 item1 location3 1
order3 item2 location3 1
order3 item3 location3 1


Because order2 does not have locations for all the items on the order, it cannot be called "stagedcomplete".

Any ideas? Kind of perplexing me, but I'm sure there's an easy solution.

Thanks in advance.

benking9987
Posting Yak Master

124 Posts

Posted - 2013-11-29 : 14:32:25
Here's a little bit better formatting for reference:

ordernumber item location stagecomplete
order1 item1 location1 0
order1 item2 location1 0
order2 item1 location2 0
order2 item2 location2 0
order2 item3 NULL 0
order2 item4 NULL 0
order3 item1 location3 0
order3 item2 location3 0
order3 item3 location3 0


Want the update statement to make it look like this:


ordernumber item location stagecomplete
order1 item1 location1 1
order1 item2 location1 1
order2 item1 location2 0
order2 item2 location2 0
order2 item3 NULL 0
order2 item4 NULL 0
order3 item1 location3 1
order3 item2 location3 1
order3 item3 location3 1
Go to Top of Page

benking9987
Posting Yak Master

124 Posts

Posted - 2013-11-29 : 15:22:43
I actually got my brain on track and found a way to do this:

UPDATE table SET stagecomplete = '1'
FROM
(
SELECT ordernumber, sum(convert(numeric(18,0),case when location IS null then '0' else '1' end)) as sumstagelocation, totallines
FROM table
GROUP BY ordernumber, totallines
HAVING sum(convert(numeric(18,0),case when location IS null then '0' else '1' end)) = totallines
)x
WHERE x.ordernumber = table.ordernumber AND table.stagecomplete = '0'


I had to add in a column called 'totallines' earlier in my process to validate the number of staged items on an order to the total number of items expected. To verify something was staged complete, if the two numbers matched, then I updated the bit column to '1'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 01:36:21
you can simplify it to just this

UPDATE t
SET stagecomplete = 1
FROM (
SELECT stagecomplete, SUM(CASE WHEN location IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY ordernumber) AS Cnt
FROM table
)t
WHERE Cnt = 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -