1

Hi Here i came across a situation in which by mistakenly Without dropping the table i have run the batch file of the table which consists of some insert statements in detail

I have a table like alert_priority consists of records like

Id priority_name --- -------------- 1 P0 2 P1 3 P2

and now by mistakenly without dropping alert_priority i have executed script file of the table which consists of some insert statements and now after executing the script my records in the table are like

Id priority_name --- -------------- 1 P0 2 P1 3 P2 1 P0 2 P1 3 P2

Now i want to delete the records which are extra(records after Id 3) and i should have all the records which are present before i have executed the script file.

Although i have an option to drop the table and execute the script file once again, I wanted to know is there any way which we can do through sql query
I have no primay keys in the table

Improve this question
4
  • If you had a primary key on ID, this wouldn't have happened in the first place...unique constraints are your friend. :) Commented Oct 15, 2012 at 7:58
  • There are several options I can think of. One you could generate a script for the table AND the data (assuming you don't have an obscene amount), and then you can just cut out the inserts you don't need (this takes about 30 seconds if you're using SSMS). truncate table and execute new script. Or you could select the top X amount (however many records there really are), insert them into a temporary table, drop table and re-create, and copy back over. Commented Oct 15, 2012 at 8:02
  • @John Woo, howeverr, there is no marked answer to the question you refer to Commented Oct 15, 2012 at 8:31
  • @aviad: This is most likely a mindlessly copy-pasted question from a completely unrelated user. We get that fairly often, and it's a mystery why people do this at all. Commented Oct 15, 2012 at 8:46

6 Answers 6

Reset to default
2

First , consider setting your ID fields as AI (auto increasment) and even PK (Primary Key).

In order to remove those duplicated rows , we will create a new table and will move all those duplicated rows to it. After that , drop that table.

CREATE TEMPORARY TABLE bad_temp AS SELECT DISTINCT * FROM alert_priority

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

you can copy all unique records into a new table, then delete the old table:

SELECT DISTINCT * INTO new_table FROM old_table
Improve this answer

1 Comment

They are not distinct, look at the ID column.
1

In SQL-Server it would be easy using ROW_NUMBER, but alas MySQL doesn't have a function like that :-(

Best way to solve it would be as follows:

  • Create a new table identical in structure to the first, but with no data.
  • Use the query: INSERT INTO name_of_new_table SELECT DISTINCT * FROM name_of_old_table
  • Drop the old table
  • Rename the new table to whatever the old table was called.
Improve this answer

Comments

1 
CREATE TABLE new_tbl(id int AUTO_INCREMENT,priority_name); INSERT INTO new_tbl select priority_name from old_tbl group by priority_name;
Improve this answer

Comments

0

To just delete the duplicate new rows and leave the old ones in place (on the basis that I assume there are already other tables whose rows refer to the original rows):-

DELETE FROM alert_priority WHERE Id IN (SELECT MaxId FROM (SELECT priority_name, MAX(Id) AS MaxId, COUNT(Id) AS CountId FROM alert_priority GROUP BY priority_name HAVING CountId > 1))
Improve this answer

Comments

0

Following query will give you all records that you want to keep:

SELECT min(id) FROM alert_priority GROUP BY priority_name HAVING count(*) > 1 OR min(id) = max(id)

To remove all duplicates, run this query:

DELETE FROM alert_priority WHERE id NOT IN ( SELECT min(id) FROM alert_priority GROUP BY priority_name HAVING count(*) > 1 OR min(id) = max(id) )
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.