insert problem duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

insert problem duplicates

Author

Topic

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 15:33:47

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-06 : 17:13:15

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   )

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 17:31:15

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

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 17:34:18

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-06 : 18:04:49

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 23:22:48

same as

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources