Regarding updating the table

Hi experts, I have the following form of data,
customer  Name                  DateSigned                         option      amount      newsletter
 
390611      Stephen disc      1999-06-17 00:00:00.000       NO              2000      art
390611      Stephen disc      1999-06-17 00:00:00.000       YES            2000      art
375222      Stephen Myer    1998-07-27 00:00:00.000       NO            2500      poster
375222      Stephen Myer    1998-07-27 00:00:00.000       NO            8250      art
375222      Stephen Myer    1998-07-27 00:00:00.000       YES            2500      poster
375222      Stephen Myer    1998-07-27 00:00:00.000       YES            8250      art

I need to update in the below way, please suggest:

customer  Name              DateSigned                            option       amount       newsletter
 
390611      Stephen disc    1999-06-17 00:00:00.000              YES            2000         art
375222      Stephen Myer    1998-07-27 00:00:00.000       YES            2500        poster
375222      Stephen Myer    1998-07-27 00:00:00.000       YES            8250        art

Please suggest
sqlcuriousAsked:
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.

Jim HornMicrosoft SQL Server Data DudeCommented:
So it appears that you're trying to delete all duplicates where OPTION='YES' and there are OPTION='NO' rows?
;with a as (
   SELECT Customer, Option, COUNT(DISTINCT Option) as the_count
   FROM flights
   GROUP BY Customer, Option
   HAVING COUNT(DISTINCT Option) > 1
)
DELETE FROM a WHERE option='NO'

Open in new window


Proof, copy-paste the below T-SQL into your SSMS, execute it to verify it works, and modify to meet your needs
CREATE TABLE #tmp (customer varchar(100), Name varchar(100), DateSigned datetime, yesno varchar(5), amount money, newsletter varchar(10))

INSERT INTO #tmp (customer, name, DateSigned, yesno, amount, newsletter)
VALUES 
	(390611, 'Stephen disc', '1999-06-17 00:00:00.000', 'NO', 2000, 'art'), 
	(390611, 'Stephen disc', '1999-06-17 00:00:00.000', 'YES', 2000, 'art'),
	(375222, 'Stephen Myer', '1998-07-27 00:00:00.000', 'NO', 2500, 'poster'),
	(375222, 'Stephen Myer', '1998-07-27 00:00:00.000', 'NO', 8250, 'art'),
	(375222, 'Stephen Myer', '1998-07-27 00:00:00.000', 'YES', 2500, 'poster'),
	(375222, 'Stephen Myer', '1998-07-27 00:00:00.000', 'YES', 8250, 'art')

;with a as (
   SELECT customer, yesno, COUNT(DISTINCT yesno) as the_count
   FROM #tmp
   GROUP BY customer, yesno
   HAVING COUNT(DISTINCT yesno) > 1
)
DELETE FROM #tmp WHERE yesno='NO'

SELECT * FROM #tmp

Open in new window


btw, Option is a reserved word so I changed the column to yesno

Here's an article called SQL Server Delete Duplicate Rows Solutions which is an image and SQL-heavy demo, where the 'DELETE #1' section builds up to how to pull this off.
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Hi Jim,

Shouldn't the delete statement in your second code window be:

DELETE FROM a WHERE yesno='NO'

Open in new window


replacing #tmp with a.

Neat little trick by the way, using CTE to delete records from the main table. Didn't know that was possible.
Jim HornMicrosoft SQL Server Data DudeCommented:
Nope, @tmp worked in my SSMS.

>Neat little trick by the way, using CTE to delete records from the main table.
Yep, when I was doing the project behind the article I had a real hard time deleting duplicates where there was custom logic on which row lives and which row dies, mostly by trying to use subqueries, and the CTE was a wickedly simple way to pull it off.   Usually there's a RANK or ROW_NUMBER involved somewhere, but this example didn't need one.

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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Ok, if that's the case, then why do you use the CTE in the second example? You don't reference it at all in the delete statement.

It's something I'll definitely remember. Learning somethign new every day :D.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe it was only a coincidence but by the sample of data I need to agree with Koen.
A simple DELETE FROM Table WHERE option='NO' would do the job. Jim's solution is more elaborated but it's more secure in the case that there's a record without duplicates.
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Don't get me wrong, I'm not questioning Jim's solution, I'm just trying to understand it. I guess I kinda hijacked this question for my own learning :p. I still don't see why in Jim's second example the CTE is there if it's not referenced in the delete statement. Shouldn't it delete from "a" instead of #tmp?
Jim HornMicrosoft SQL Server Data DudeCommented:
Gentlemen, let's let the asker speak to his own question, especially since 'I need to update in the below,' can be interpreted multiple ways.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure, Jim.
My observation it was more for the Author of this question to show him how important is to provide correct information or he can mislead us to a wrong answer.
Cheers.
sqlcuriousAuthor Commented:
Hi all, thanks a lot for the comments, thanks Jim for the solution, sorry I should have been more elaborate, this is not working in my case, I didnot want all 'No's to be deleted in the table, I have other records too in the table that donot have duplicate customer information, I want them intact, for ex:

 390611      Stephen disc      1999-06-17 00:00:00.000       NO              2000      art
 390611      Stephen disc      1999-06-17 00:00:00.000       YES            2000      art
 375222      Stephen Myer    1998-07-27 00:00:00.000       NO            2500      poster
 375222      Stephen Myer    1998-07-27 00:00:00.000       NO            8250      art
 375222      Stephen Myer    1998-07-27 00:00:00.000       YES            2500      poster
 375222      Stephen Myer    1998-07-27 00:00:00.000       YES            8250      art
 390411      Martha jenn       1979-06-17 00:00:00.000        NO            2000       art
 375342      stewart Matt       1994-07-27 00:00:00.000       YES            2500       poster
 356222      Timber Justin      1996-07-27 00:00:00.000       NO             8250       art

I want the results as:
390611      Stephen disc    1999-06-17 00:00:00.000              YES            2000         art
 375222      Stephen Myer    1998-07-27 00:00:00.000       YES            2500        poster
 375222      Stephen Myer    1998-07-27 00:00:00.000       YES            8250        art
 390411      Martha jenn       1979-06-17 00:00:00.000        NO            2000       art
 375342      stewart Matt       1994-07-27 00:00:00.000       YES            2500       poster
 356222      Timber Justin      1996-07-27 00:00:00.000       NO             8250       art


If you see, customers Martha, Stewart and Timber's information is intact irrespective of their option being 'YES' or 'NO' since they donot have diuplicate records. I hope I am making sense now, thanks
sqlcuriousAuthor Commented:
I've requested that this question be deleted for the following reason:

I think I figured this out, had to use a left outer join in the first place before getting this data, that solved the issue, thanks for your inputs thought
Jim HornMicrosoft SQL Server Data DudeCommented:
Objection.  Show us what you figured out, as I don't see any references or requirements on multiple tables that would require a specific JOIN.
sqlcuriousAuthor Commented:
Thanks!
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 2008

From novice to tech pro — start learning today.