Update lines with subquery - SQL Server Forums

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

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'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources