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
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.
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.
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.