Solved

Regarding updating the table

Posted on 2014-11-11
14
76 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
Comment Utility
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 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now