0

This is an correction of this question now I properly understand what I need to do.

I have a table with films and the dates they were shown on along with other info in other columns, in MySQL.

So relevant columns are...

FilmID

FilmName

DateShown

The dates are stored as Unix timestamps.

I currently have multiple instances of films that were shown on different dates yet all other information is the same.

I need to copy the dates of the duplicate films into a new table matching them up to the film ID. Then I need to remove the duplicate film rows from the original table.

So I have created a new table, Film_Dates with the columns

FilmDateID
FilmID
Date

Can anyone help with the actual sql to do this.

Thank you.

Community
  • 1
  • 1
HowardHosk
  • 33
  • 4

1 Answers1

1

to start with:

insert into filmdateid (filmid, `date`)
  select filmid, dateshown 
    from films

and that should populate your new table.

alter ignore table films
  add unique (filmid)

This will enforce uniqueness for filmid, and drop all duplicates, keeping just the one row. If this fails with a 'duplicate entry' error, you will need to run this command, and then try the alter again.

set session old_alter_table=1

As it seems mysql is moving away from being able to do it this way.

Lastly, you need to get rid of your dateshown column.

alter table films
  drop column dateshown

Please make sure you have a backup before you attempt any of this. Always best to be safe.

since filmid is not duplicated, only filmname, there are some extra steps

first, create the filmdates table:

create table filmdates as
  select filmname, dateshown
    from films;

Then add a filmid column:

alter table filmdates add column filmid integer;

And a unique index on (filmname, dateshown)

alter ignore table filmdates add unique(filmname, dateshown);

Then we add a unique index on films(filmname) - since its the only value that really gets duplicated.

alter ignore table films add unique(filmname);

Now that we're setup, we populate the filmid column in the new table, with maching values from the old.

update films f 
  inner join filmdates fd
    on f.filmname = fd.filmname
  set fd.filmid = f.filmid;

Now we just need to cleanup, and get rid of the redundant columns (films.dateshown and filmdates.filmname).

alter table filmdates drop column filmname;
alter table films drop column dateshown;

demo here

pala_
  • 8,901
  • 1
  • 15
  • 32
  • Just had a chance to look at this. Thank you for taking the time I will try it tonight...but since FilmId is already unique I can copy the film name to the new table too, remove the duplicates using that and then delete the film name column from the new table. I think that should work! – HowardHosk Apr 28 '15 at 14:06
  • ah. filmid being unique already means we have to work only with filmname to detect the duplicates then? – pala_ Apr 28 '15 at 14:52
  • Yes exactly just the names for the duplicate's – HowardHosk Apr 28 '15 at 22:37
  • well that changes things a little. i've updated the answer with a plan that better suits your updated requirements – pala_ Apr 28 '15 at 23:59