February 21, 2014 at 1:21 pm
Hello all,
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below you will find the code I am running which another poster helped me on earlier this week. Thanks again Dwain!
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int,
Blocked int
)
Insert into #Items
(
ItemNo , SearchNo ,Historical, Blocked
)
Values
('QBGFEP2050 CHD', 'QBGFEP2050 CHD', 009, 0),
('QBGFEP2050 CH DIS', 'QBGFEP2050 CHD', ' ', 0),
('ATMR10 MFZ','ATMR10 MFZ',002, 0),
('ATMR10 MFZ N','ATMR10 MFZ', ' ', 0)
select * from #Items
--this query shows me the proper ItemNo I need to update
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)
FROM
(
SELECT ItemNo, SearchNo
,rn=ROW_NUMBER() OVER (PARTITION BY SearchNo ORDER BY Historical)
FROM #Items
) a
GROUP BY SearchNo
HAVING COUNT(*) > 1;
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1
Where Exists
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)
FROM
(
SELECT ItemNo
,rn=ROW_NUMBER() OVER (PARTITION BY SearchNo ORDER BY Historical)
FROM #Items
) a
GROUP BY ItemNo
HAVING COUNT(*) > 1
)
Why is the update changing each record?
How can I change the update to choose the correct records?
Thank you for your input and ideas.
February 21, 2014 at 1:43 pm
You are updating every row because there is nothing to tie the rows together. Your logic basically says, if there are any rows that meet this criteria then update the whole table.
I am not entirely sure what your business rules are here but you could greatly simplify this with a cte.
with NumberedSet as
(
SELECT ItemNo, Blocked, rn = ROW_NUMBER() OVER (PARTITION BY SearchNo ORDER BY Historical)
FROM #Items
)
update NumberedSet
set Blocked = 1
where rn > 1;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2014 at 7:43 am
Sean,
thanks for your post. I agree with the usage of the CTE. I'll definitely use that method. After doing some data analysis on your script and some changes on my side I realize that I forgot to mention a few other pieces of criteria.
I need to run the update against only duplicate records that have no value in a column named Historical.
Below is an updated temp table with the additional fields to test against.
Following that is the CTE with the inner query selecting the duplicate records that have no historical values. The inner query produces the correct results but the CTE fails due to the aggregate Group By.
Would you mind taking a look at the updated CTE and point out how I can get the correct results?
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical varchar (14),
Blocked int,
DoNotUse int
)
Insert into #Items
(
ItemNo , SearchNo ,Historical, Blocked, DoNotUse
)
Values
('QBGFEP2050 CHD', 'QBGFEP2050 CHD', '009', 0, 0),
('QBGFEP2050 CH DIS', 'QBGFEP2050 CHD', ' ', 0,0),
('ATMR10 MFZ','ATMR10 MFZ','002', 0,0),
('ATMR10 MFZ N','ATMR10 MFZ', ' ', 0,0),
('ATMR11 MFZ N','ATMR11 MFZ', ' ',0,0),
('GE7M 1 N','GE7M','003',0,0)
select * from #Items
Here is the updated CTE which doesn't work.
with NumberedSet as
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
,SearchNo
,COUNT(*) as DupCount
,Blocked
,DoNotUse
FROM
(
SELECT ItemNo, SearchNo,Blocked,DoNotUse, Historical
,rn=ROW_NUMBER() OVER (PARTITION BY SearchNo ORDER BY Historical)
FROM #Items
) a
GROUP BY SearchNo,Blocked, DoNotUse
HAVING COUNT(*) > 1
)
update NumberedSet
set Blocked = 1, DoNotUse = 1
February 25, 2014 at 7:56 am
You said the inner query returns the rows you want to update. That is basically the same thing I posted previously. Not sure what the point of the outer query is.
This works according to what you stated.
with NumberedSet as
(
SELECT ItemNo, SearchNo,Blocked,DoNotUse, Historical
,rn=ROW_NUMBER() OVER (PARTITION BY SearchNo ORDER BY Historical)
FROM #Items
)
update NumberedSet
set Blocked = 1, DoNotUse = 1
where rn > 1
If the above query is not correct can you please explain what is not correct?
I need to run the update against only duplicate records that have no value in a column named Historical.
Just so you understand, ALL of your rows have a value in Historical. Some of them have numbers and the other all have a single space.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2014 at 7:58 am
Sean,
I think this will work. I just changed the sort order of Historical in the ranking function.
with NumberedSet as
(
SELECT ItemNo, Blocked, DoNotUse, Historical, rn = ROW_NUMBER() OVER (PARTITION BY SearchNo ORDER BY Historical DESC)
FROM #Items
)
update NumberedSet
set Blocked = 1, DoNotUse = 1
where rn > 1 and Historical = ' '
February 25, 2014 at 8:00 am
kwoznica (2/25/2014)
Sean,I think this will work. I just changed the sort order of Historical in the ranking function.
with NumberedSet as
(
SELECT ItemNo, Blocked, DoNotUse, Historical, rn = ROW_NUMBER() OVER (PARTITION BY SearchNo ORDER BY Historical DESC)
FROM #Items
)
update NumberedSet
set Blocked = 1, DoNotUse = 1
where rn > 1 and Historical = ' '
Cool. Thanks for posting your solution and letting me know you figured it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2014 at 8:08 am
Sure, so I'll explain what I found in my data analysis.
The original CTE you wrote would have set the flag for the Blocked and DoNotUse columns. I originally did not post those columns in my temp table therefore you didn't know about them. By changing the sort for Historical to DESC it now works. I also added AND Historical = ' ' to the where clause in the CTE just to be safe.
The other situation I needed to take into account was for records which did not have duplicates but had a Historical value = ' '. In regards to your CTE this really didn't matter since you had set the rn > 1 but I'm just mentioning it.
Thank you for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply