Solved

Regarding updating the table

Posted on 2014-11-11
14
79 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 12

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
 
LVL 12

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 46

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 12

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 46

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Updating Table/Fields Documentation 3 74
Sql query to Stored Procedure 6 39
SQL Transaction logs 8 28
Storage Spaces 3 39
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

28 Experts available now in Live!

Get 1:1 Help Now