SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 insert problem duplicates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  15:33:47  Show Profile  Reply with Quote
I execute the following query to insert a new record in the customer table with Subscription_code 'AWD', the problem is I get duplicate customer records when a customer has both PDD and TV with Subscription_state = Y. How do i avoid that?

INSERT INTO Customer (List_ID, Master_Customer_id, SS_ID, Subscription_code, Subscription_state, Mod_Date, Transmission_flag, Transmission_date, Verified_flag, Verified_date)
(Select List_ID, Master_Customer_id, SS_ID,'AWD', Subscription_state, Add_Date, Mod_Date, Transmission_flag, Transmission_date, Verified_flag, Verified_date
FROM Customer
WHERE (Subscription_code = 'PDD' OR Subscription_code = 'TV') AND Subscription_state = 'Y')

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 03/06/2013 :  17:13:15  Show Profile  Reply with Quote
You have at least two choices, depending on the data.

1. If all the columns that you are inserting are identical in both rows whether subscription code is PDD or TV, then you can use a distinct clause in the select
(Select DISTINCT List_ID, Master_Customer_id, SS_ID,'AWD', Subscription_state,...


2. If they values can differ - for example, let us say Add_Date column for PDD is different from Add_Date column for TV. Then, you have to make a decision as to which value of Add_Date you want to insert in the one row that will be inserted. Let us say you want the latest date out of the two. Then use a MAX function together with group by clause
INSERT INTO Customer
  (
    List_ID,
    Master_Customer_id,
    SS_ID,
    --YOU NEED TO GIVE A COLUMN NAME HERE FOR THE 'AWD TO GO IN
    Subscription_code,
    Subscription_state,
    Mod_Date,
    Transmission_flag,
    Transmission_date,
    Verified_flag,
    Verified_date
  )(
       SELECT List_ID,
              Master_Customer_id,
              SS_ID,
              'AWD',
              Subscription_state,
              MAX(Add_Date),
              Mod_Date,
              Transmission_flag,
              Transmission_date,
              Verified_flag,
              Verified_date
       FROM   Customer
       WHERE  (Subscription_code = 'PDD' OR Subscription_code = 'TV')
              AND Subscription_state = 'Y'
       GROUP BY
              List_ID,
              Master_Customer_id,
              SS_ID,
              Subscription_state,
              Mod_Date,
              Transmission_flag,
              Transmission_date,
              Verified_flag,
              Verified_date   )
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  17:31:15  Show Profile  Reply with Quote
Tried those, didn't work. I still get the duplicate records as shown below. When I say duplicate records I get two records with the same Subscription_code AWD. I don't want that. I only want one record with AWD when (Subscription_code = 'PDD' OR Subscription_code = 'TV') AND Subscription_state = 'Y') condition comes true.

8304006 1 03275330 0 AWD Y
8304007 1 03275330 0 AWD Y
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 03/06/2013 :  17:34:18  Show Profile  Reply with Quote
FYI, this is the table example with customer id being 03275330 and there could be multiple customers with different subscription codes for a single customer id. Hope I am clear

4363835 1 03275330 0 B2B N
4363836 1 03275330 0 CC Y
4363837 1 03275330 0 CU N
4363838 1 03275330 0 FND Y
4363839 1 03275330 0 MA N
4363840 1 03275330 0 MB Y
4363841 1 03275330 0 MNE N
4363842 1 03275330 0 MP N
4363843 1 03275330 0 MR N
4363844 1 03275330 0 MTL N
4363845 1 03275330 0 PD Y
4363846 1 03275330 0 PDD Y
4363847 1 03275330 0 SO Y
4363848 1 03275330 0 TV Y
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 03/06/2013 :  18:04:49  Show Profile  Reply with Quote
I was only showing an example of how to change your code if Add_Date column had different values for a pair of PDD and TV. Based on your data, you will need to write the SELECT statement so it returns only one row for each PDD and TV combination.

I am unable to interpret the data you posted because there are no column headings, and there are fewer columns than you have in the select statement.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/06/2013 :  23:22:48  Show Profile  Reply with Quote
same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183515

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000