[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Update Columns in duplicate records

Hello, would like help coming up with correct query. Trying to remove duplicates, about 16,000 rows. I would like to update the username column to username+'bak' and Archive='Y'. Only want to update the rows with the smaller date_creation date, I think my Syntax is off please help . Here is what I have so far. The Select query at the bottoms finds the rows perfectly just trying to incorporate into Update Statement.

UPDATE USERS SET USERNAME=USERNAME+'BAKMC',ARCHIVE='Y'
FROM         USERS
WHERE
DATE_CREATION= MIN(DATE_CREATION)
GROUP BY USERNAME
HAVING      (COUNT(*) > 1)


Select Statement that finds the right records to update:
SELECT     USERNAME, MIN(DATE_CREATION) AS DATE_CREATION
FROM         USERS
GROUP BY USERNAME
HAVING      (COUNT(*) > 1)
0
marcuschang
Asked:
marcuschang
  • 3
  • 2
1 Solution
 
mcp111Commented:
UPDATE USERS SET USERNAME=USERNAME+'BAKMC',ARCHIVE='Y'
FROM         USERS A
JOIN
(SELECT     USERNAME, MIN(DATE_CREATION) AS DATE_CREATION
FROM         USERS
GROUP BY USERNAME
HAVING      (COUNT(*) > 1)) DUPUSERS B
WHERE A.USERNAME=B.USERNAME
AND A.DATE_CREATION=B.DATE_CREATION
0
 
marcuschangAuthor Commented:
Getting this error Error message from update command
0
 
Scott PletcherSenior DBACommented:
...
(SELECT     USERNAME, MIN(DATE_CREATION) AS DATE_CREATION
FROM         USERS
GROUP BY USERNAME
HAVING      (COUNT(*) > 1)) B --<<--
...

No points just for that please! :-)
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
marcuschangAuthor Commented:
Removed DUPUSERS now getting message that there is a syntax error with WHERE clause
0
 
marcuschangAuthor Commented:
Error after remove DUPUSER off having line Error after remove DUPUSER off having line
0
 
mcp111Commented:
UPDATE USERS SET USERNAME=USERNAME+'BAKMC',ARCHIVE='Y'
FROM         USERS A
JOIN
(SELECT     USERNAME, MIN(DATE_CREATION) AS DATE_CREATION
FROM         USERS
GROUP BY USERNAME
HAVING      (COUNT(*) > 1)) B
ON A.USERNAME=B.USERNAME
AND A.DATE_CREATION=B.DATE_CREATION
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now