SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update lines with subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

benking9987
Posting Yak Master

121 Posts

Posted - 11/29/2013 :  14:29:23  Show Profile  Reply with Quote
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

121 Posts

Posted - 11/29/2013 :  14:32:25  Show Profile  Reply with Quote
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

121 Posts

Posted - 11/29/2013 :  15:22:43  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/30/2013 :  01:36:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000