SQL Server Performance Forum – Threads Archive

deleting values before insert

Hi I am trying to enter data into a table and i use the bulk insert function to enter data from the text file into a temporary table. I want to insert this temp table data into a main table (of columns a,b,c and d with primary key on a,b and d). Some times i want to overwrite the date in the main table with the new data in the temp table if the value in column c is different. I have tried deleting that value from the main table and then inserting the whole temp table data, however i don’t just delete the duplicate value i delete the whole table. How do i go about the delete SQL statement for deleting any duplicate values? Thanks CE

---

To locate the duplicate rows, you can use the following: SELECT a,b,c,d, Count(*) [Count]
FROM MainTable
GROUP BY a, b, c, d
HAVING Count(a)>1 and Count(b)>1 and Count(c)>1 and Count(d)>1 You may use HAVING Count(*)>1, too. But how to delete the extra lines? I’m thinking of adding an ID column to the table and then find a way to locate and delete duplicate values. (I assume that you want to keep at least one row of the duplicate values.
Farhad R
[email protected]

---

The duplicate values are those values in the temporary table that match the a,b and d columns of the main table. I want to write a statement that will delete (from the main table) the values which are the same as the ones in the temporary table. I have tried: DELETE FROM <Main table>
SELECT DISTINCT a, b, c, d FROM <temporary table> a1
WHERE exists (SELECT a, b,c, d FROM <Main Table> WHERE
a= a1.aand b= a1.b and c= a1.c) This however delete all the existing values in the main table and not just the rows that match in the temporary table. Any idea? CE

---

Sorry i realised what i worte in my message was wrong – just to confuse everyone. The statement i have been using is: DELETE FROM <Main table>
SELECT DISTINCT a, b, c, d FROM <temporary table> a1
WHERE exists (SELECT a, b,c, d FROM <Main Table> WHERE
a= a1.aand b= a1.b and d= a1.d) I have been trying loads of things and none of them work so any help would be very much appreciated. Thanks
CE

---

These are two command not one! So the first one DELETEs the <Main table> and the second one is a SELECT statement. That’s why you lose all your data in <Main table>. You need to use INNER JOIN. I’ll write the command. Farhad R
[email protected]

---

Here is the command to delete duplicated values in <Main> which are already in <Temp>: DELETE <Main table>
FROM <Main table> M INNER JOIN <Temp table> T
ON M.a = T.a AND
M.b = T.b AND
M.d = T.d
Farhad R
[email protected]

---

Thanks it works great CE

---

]]>