Solved

Regarding updating the table

Posted on 2014-11-11
14
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 50

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 50

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Master DB with Masterkey 1 40
Clone table from one server.database to another server.database 24 59
tempdb log keep growing 7 55
Many to one in one row 2 46
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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