question

roshan avatar image
roshan asked

How to Delete , Update and Insert for this scenario

Hi All, This is the scenario for which i have trying to find a solution for last 2 days. Please help me out Scenario: I have @person and @temp table with some reocreds as shown below. i have some incorrect data in the @Person table which should be correcteed using @temp table. @temp table shows which are the correct id that should exist in the @person table.Now what i am trying to do is, if i have correct_id in the @person table then the corresponding wrong_id's should be deleted. ex: select * from @temp where correct_id = 1 results ------ 1,2 1,3 since 1 is the `correct_id`, id = 2,3 in the @person table should be deleted and and 1 should be kept as it is. Similarly for `correct_id` = 9, the `wrong_id` = 7(this may sound wierd because i dont have 9 in @person table. i have some other table from where i get value 9. So for now lets continue as i said) since id =9 does not exist in @person table, we need to insert a record into @person table with values " 9,'jane'"(Here the name should come form the id = 7). How do i get this??? declare @person table( id int, name varchar(20)) insert into @person select 1,'sam' union all select 2,'sam' union all select 3,'sam' union all select 4,'robert' union all select 5,'robert' union all select 6,'robert' union all select 7,'jane' union all select 8,'mike' declare @temp table( correct_id int, wrong_id int) insert into @temp select 1,2 union all select 1,3 union all select 4,5 union all select 4,6 union all select 9,7 union all select 10,8 select * from @person select * from @temp
sql-server-2008t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
To perform the delete you could do the following: DECLARE @person TABLE (id int, name varchar(20)) INSERT INTO @person SELECT 1,'sam' UNION ALL SELECT 2,'sam' UNION ALL SELECT 3,'sam' UNION ALL SELECT 4,'robert' UNION ALL SELECT 5,'robert' UNION ALL SELECT 6,'robert' UNION ALL SELECT 7,'jane' UNION ALL SELECT 8,'jane' DECLARE @temp TABLE (correct_id int, wrong_id int) INSERT INTO @temp SELECT 1,2 UNION ALL SELECT 1,3 UNION ALL SELECT 4,5 UNION ALL SELECT 4,6 UNION ALL SELECT 7,8 DELETE p FROM @person p WHERE NOT EXISTS (SELECT * FROM @temp t WHERE t.correct_id= p.id) This would use the "cleanup" table @temp to identify the correct entries, deleting all @person entries that don't have a corresponding `correct_id`. This will not perform the second part of your validation logic (the part about 9 not being there yet etc.) The easiest way to do that would be to insert all entries into the @person table, then create the corresponding validation entry in @temp and use the script I gave above.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

roshan avatar image roshan commented ·
The suggestion you gave me to insert all entries into @person table is even harder to find them and then insert. I was looking for a solution which would agree with my condition. If 9 does not exist it should be inserted with name = 'jane'(deleting id = 7). Can there be another solution??
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
But your example shows that 7,Jane is the valid data and nothing to do with 9. You have to have some sort of relation between the 7,Jane and 9,Jane - maybe it is hard for you to show that in t-sql, but maybe we can help.
0 Likes 0 ·
roshan avatar image roshan commented ·
I am sorry William, now i have edited my question(data). These no's for(@person table) comes from other table. All i know is 7 should be replaced with 9 so that it would give correct mapping.Similarly 8 should be replace with 10.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Keep in mind that SQL server doesn't create statistics for table variables, and if you are handle large amount of data, you should consider using temporary tables instead. SQL server assumes it's only one record in a table variable, and that may cause an unefficient execution plan.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 avatar image
Slick84 answered
I think you are trying to do two things here. You are trying to delete dupes and then update the id values to be the same between the temp table and the persons table. If I understand that correctly, then you could use a COUNT function to find all the dupes, based on the name and if greater than zero, you could delete those dudes. Of course, here names is a sensitive matter as its not a unique value so it would be better if you had a unique identifier of some sorts attached to each unique record to find these dupes. Once the dupes are out, you should proceed with your update statement and join on that unique identifier and update the id values from the temp table to the persons table.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.