Hello all,
Suppose I have a table (towns) with the following fields: island, town
England - London
England - Liverpool
Azores - Ponta Delgada
Madeira - Funchal
Ireland - Dublin
Ireland - Cork
...
What I want to do is to get a distinct list of all the islands and insert them into a new table (islands) that is already created with the fields ID (index, unique,auto_increment), name
I thought to use something like:
INSERT INTO islands(name) SELECT DISTINCT island FROM islands
but mysql only inserts the first row and none more. I think it has something to do with the ID unique field from the islands table, but I still can't figure it out.
any suggestions?..... please?
Suppose I have a table (towns) with the following fields: island, town
England - London
England - Liverpool
Azores - Ponta Delgada
Madeira - Funchal
Ireland - Dublin
Ireland - Cork
...
What I want to do is to get a distinct list of all the islands and insert them into a new table (islands) that is already created with the fields ID (index, unique,auto_increment), name
I thought to use something like:
INSERT INTO islands(name) SELECT DISTINCT island FROM islands
but mysql only inserts the first row and none more. I think it has something to do with the ID unique field from the islands table, but I still can't figure it out.
any suggestions?..... please?