Update table with duplicate records

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.

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.

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;

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources