Mysql add mult-column index with repeated rows -

-

having next table:

id -> incremental field_1 -> foreignkey field_2 -> foreignkey

i want add next index 

alter table my_table add unique index(field_1, field_2);

how ever have (due bad application validation) have lot of repeated rows (by repeated mean same field_1 , same field_2, having id difference)

the table has 60,000 rows so... removing field field hard.

how can apply index , remove every duplicated row?

create table mytable2 mytable;  insert mytable2  select max(id), field_1, field_2  mytable  group field_1, field_2;  rename table mytable mytable_old, mytable2 mytable;  alter table my_table add unique index(field_1, field_2)

Comments

Post a Comment

Popular posts from this blog

silverlight - Applying a style to ItemContainerStyle in C# -

-
when i'm applying style listbox.itemcontainerstyle in c# isn't making change @ runtime. i'm using; mylistbox.itemcontainerstyle = (style)(app.current.resources["newitemstyle"]); however other styles being supplied, such buttons etc i'm going mybutton.style = (style)(app.current.resources["mymainbuttonstyle"]); i don't errors either, nothing , reverts default system style. i using app.current.resources instead of resources because "newitemstyle" wasn't defined in whole application. mistake.
Read more

c# - NullReferenceException in MySqlClient.NativeDriver -

-
i have long running program exectues mysql-queries every 5 minutes (timed thread). after has run approximately 10 hours nullreferenceexception thrown following stacktrace: at mysql.data.mysqlclient.nativedriver.getresult(int32& affectedrow, int32& insertedid) bei mysql.data.mysqlclient.driver.getresult(int32 statementid, int32& affectedrows, int32& insertedid) @ mysql.data.mysqlclient.driver.nextresult(int32 statementid) @ mysql.data.mysqlclient.mysqldatareader.nextresult() @ mysql.data.mysqlclient.mysqldatareader.close() @ mysql.data.mysqlclient.mysqlcommand.resetreader() @ mysql.data.mysqlclient.mysqlcommand.executereader(commandbehavior behavior) @ mysql.data.mysqlclient.mysqlcommand.executenonquery() @ myownprogram.mysql.senddata(string sqlcommmand) the senddata method following (lockdb of type object) public void senddata(string sqlcommmand) { try { using(mysqlcommand command = connection.createcommand()...
Read more

php - Updating recent updates - Problem in procedure - Any other approach? -

-
i have section in website called recent updates. in show latest 5 updates @ first when page rendered. when new update done @ real time, new update appended top of updates list , last 1 deleted. my approach: for problem, im doing is, first renders page last 5 recent updates. fetching updates database. when update made in realtime, stored in database flag = 0. to find out whether update made, have javascript calls ajax page every 4 seconds. ajax page checks in db whether records flag=0 present. if of there, return update flag=0 , set flag 1. problem: it working fine on single system. havent tested on multiple system @ same time. i fears, each update can viewed single user , flag set 1. other users may not it. is there alternate approach in solving issue?. may changing query or may yield goodness. expecing experts here.. appreciated. each update stored timestamp of when created. javascript figures out timestamp of latest update displayed on site. ajax requ...
Read more