sql syntax issue: Need to determine duplicate records and remove dups

I have a table where duplicate records where entered, note there are no indexes on the table due to outside controls.

 UPDATE `metrics`.`JACKPOT_FINAL_REPORT`, (select distinct  SNAPSHOT_DAY, PLANNING_DATE, ORDER_DATE, ORDER_ID, ORDER_CONDITION, ASIN, FNSKU, IOG, MERCHANT_ID, UNITS, ADJ_DOWN_UNITS, JACKPOT_REASON, PREV_GOOD_PLAN_DATE, PREV_GOOD_FC_NAME, PREV_GOOD_SUPPLY_TYPE, PREV_GOOD_TRANSSHIP_FROM, PREV_GOOD_UNIT, PREV_GOOD_LINE_ITEM_ID, PREV_GOOD_ORDER_ARCHIVE_ID, PREV_GOOD_SUPPLY_SOURCE_COUNT, PDD, EAD, HTRC, HAZMAT_EXCEPTION, COUNTRY, STATE, CITY, ZIP_CODE, ADDRESS_TYPE, SHIP_WEIGHT, JACKPOT_PARENT_REASON, SAMPLE_FLYPAPER_REASON, P_C_A, NEW_TO_SNAPSHOT_DAY, ORDER_AGE, DAYS_IN_JACKPOT from JACKPOT_FINAL_REPORT  where snapshot_day = '2015-10-31') 
Set SNAPSHOT_DAY = '2016-10-31;

Open in new window


My thought was to determined the distinct records then change the snapshot_day , then remove the other records for the old date, then modify the original changed date back to original date.

since the records does not have indexes  I am looking for suggestions on the best approach to remove dups.

urgent help needed.

current syntax issue is on the set statement.

thnks
K
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachiekCommented:
Check this article.

https://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/

I think this is best way you can remove those duplicate rows.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sachiekCommented:
If you want much more simple solution..

WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank'
             FROM Table)
DELETE FROM cte 
WHERE RowRank > 1

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Your UPDATE command is uncommon. It's really SQL Server? If so, didn't you miss nothing? Like for example the WHERE criteria?
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the assitance, turns out to be a permissions issue.  thanks for your time
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.