Solved

Regarding updating the table

Posted on 2014-11-11
14
81 Views
Last Modified: 2014-12-09
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
0
Comment
Question by:sqlcurious
  • 4
  • 3
  • 3
  • +1
14 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40436480
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.
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40436574
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.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40436584
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.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40436594
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.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40436855
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.
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40437021
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?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40437158
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.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40437168
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.
0
 

Author Comment

by:sqlcurious
ID: 40438161
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
0
 

Author Comment

by:sqlcurious
ID: 40448651
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
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40448652
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.
0
 

Author Closing Comment

by:sqlcurious
ID: 40488331
Thanks!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question