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 2008 Forums
 Transact-SQL (2008)
 Update table with duplicate records

Author  Topic 

taunt
Posting Yak Master

128 Posts

Posted - 2014-11-20 : 21:57:13
Hello, I'm trying to figure out how to update a table where the column SKU is duplicated by format. So far I run this query:

SELECT sku, Format
FROM update
WHERE (LEN(LabelAb) = 3)
GROUP BY sku, Format
HAVING (COUNT(*) > 1)
ORDER BY sku


That will list all the duplicate SKU with the same format. I'm trying to update the "update" table to go off the itemnumber when the SKU is duped. How would I go about doing that with a query like the above or if there's a better one to use I'll try that.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 09:31:49
Here's one way:


MERGE INTO table
USING (
SELECT sku, Format
FROM update
WHERE (LEN(LabelAb) = 3)
GROUP BY sku, Format
HAVING (COUNT(*) > 1)
ORDER BY sku ) dups
ON table.sku = dups.sku
WHEN MATCHED THEN
UPDATE SET ...
;


Note that you didn't show where you get itemnumber from. Hopefully you get the idea though.
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2014-11-21 : 16:28:41
OK another question, if I wanted to selected everything in the table with doubled sku's and matching format how can I do that I tried:
SELECT *
FROM update
WHERE (LEN(LabelAb) = 3)
GROUP BY sku, Format
HAVING (COUNT(*) > 1)
ORDER BY sku


but that will complain about not everthing is in the group by clause. How can I get it to liat every column with matching sku and format?

Thanks



quote:
Originally posted by gbritton

Here's one way:


MERGE INTO table
USING (
SELECT sku, Format
FROM update
WHERE (LEN(LabelAb) = 3)
GROUP BY sku, Format
HAVING (COUNT(*) > 1)
ORDER BY sku ) dups
ON table.sku = dups.sku
WHEN MATCHED THEN
UPDATE SET ...
;


Note that you didn't show where you get itemnumber from. Hopefully you get the idea though.

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 16:41:11
True, when you use GROUP BY, every column must be aggregated or in the GROUP BY clause. Think about it. It DOES make sense. IN your case, you'll want a subquery:


SELECT *
FROM [UPDATE] u
JOIN(
SELECT sku
, format
FROM [update]
WHERE LEN(LabelAb) = 3
GROUP BY sku
, Format
HAVING COUNT(*) > 1
ORDER BY sku)j
ON u.sku = j.sku;
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2014-11-21 : 20:47:32
This worked perfect.

Thanks

quote:
Originally posted by gbritton

True, when you use GROUP BY, every column must be aggregated or in the GROUP BY clause. Think about it. It DOES make sense. IN your case, you'll want a subquery:


SELECT *
FROM [UPDATE] u
JOIN(
SELECT sku
, format
FROM [update]
WHERE LEN(LabelAb) = 3
GROUP BY sku
, Format
HAVING COUNT(*) > 1
ORDER BY sku)j
ON u.sku = j.sku;


Go to Top of Page
   

- Advertisement -