MySQL Insert prevent double

Question

I am looking for a way to insert rows that don't already exist. I have read about unique keys, i also added a id (unique auto inc) column. And i have tried alot of different methods - but i can't get it to work.

What i tried so far:

INSERT INTO programmes (station_id, programme_start, programme_end, title, subtitle, long_description, short_description, rating, imdb, episode, season, year, categories, icon, actors, writers, directors)
VALUES
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  station_id = VALUES(station_id),
  programme_start = VALUES(programme_start)

i also tried:

ON DUPLICATE KEY UPDATE

but i keep getting #1064 errors saying there's something wrong at ''

I dont know what i am doing wrong.. I am using the database to update tv-guide information daily - and i get these data from different api's. Now these api responses sometimes contain dubbles (if a program spans over 2 days).

I also tried to use if and else but everything i try fails with error #1064

When i try this query:

INSERT INTO `programmes` (`station_id`, `programme_start`, `programme_end`, `title`, `subtitle`, `long_description`, `short_description`, `rating`, `imdb`, `episode`, `season`, `year`, `categories`, `icon`, `actors`, `writers`, `directors`)
    VALUES (1, '2018-10-16 00:00:00', '2018-10-16 00:20:00', 'NOS Journaal', '', 'Met het laatste nieuws, gebeurtenissen van nationaal en internationaal belang en de weersverwachting voor vandaag.', 'Met het laatste nieuws, gebeurtenissen van nationaal en internationaal belang en de weersverwachting voor vandaag.', '', '', '0', '0', '2017', 'Nieuws,', 'https://wp18-images-nl-dynamic.horizon.tv/EventImages/36652793.l.3dc8d9a6db753ad283c5909e6b29a7e61fb51d51.jpg', '', '', '')
    ON DUPLICATE KEY UPDATE title = 'Test UPDATE'

it say's it inserted, but this row already exists, so it should be updated instead of inserting a new one.

My database contains several tables: - Providers (contains all the tv-guide / tv providers) - Stations (all tv stations) the id of the station is linked to the programmes table, so i can find all programmes for a station. - programmes (contains all programmes for all stations).

My programmes table is made with this query:

CREATE TABLE programmes (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    station_id INT NOT NULL,
    programme_start DATETIME NOT NULL,
    programme_end DATETIME NOT NULL,
    title TEXT NOT NULL,
    subtitle TEXT NOT NULL,
    long_description TEXT NOT NULL,
    short_description TEXT NOT NULL,
    rating VARCHAR(255) NOT NULL,
    imdb VARCHAR(255) NOT NULL,
    episode INT NOT NULL,
    season INT NOT NULL,
    year VARCHAR(255) NOT NULL,
    categories TEXT NOT NULL,
    icon TEXT NOT NULL,
    actors TEXT NOT NULL,
    writers TEXT NOT NULL,
    directors TEXT NOT NULL
    );

the providers table and the stations table use the unique id as reference. This way i can find all stations for provider id=1, or id=2, and this way i can get all programmes for station id=1, or id=2 etc.

We are always glad to help and support new coders but ***you need to help yourself first. :-)*** After [**doing more research**](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Jay Blanchard, Oct 16 '18 at 19:27
Okay i will change my question. My apollogies.. i am trying to work this out now for several day's.. and its getting under my skin ;) — Ernst Reidinga, Oct 16 '18 at 19:28
The first column (id) but because the table is cleared and filled daily with updating the tv-guide data. I create a backup of the table, and execute a `delete * from programmes` and then i reset the auto increment — Ernst Reidinga, Oct 16 '18 at 19:49
you are right, my bad - but i cant find a query that can insert preventing doubles — Ernst Reidinga, Oct 16 '18 at 19:59
Have a look at https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html if you haven't yet. — Funk Forty Niner, Oct 16 '18 at 19:59
I looked at that page - but i dont understand - im trying for several day's now.. but i dont understand how it works.. (see my query at the end of the post) — Ernst Reidinga, Oct 16 '18 at 20:01
if there's an "id" column for example which is auto_increment'ed, it shouldn't be part of a query, should this be the case. You may want to update your question again to contain what the db/table schema is and values. — Funk Forty Niner, Oct 16 '18 at 20:11
there is a 'id' column and it is auto incremented. I dont set the id when inserting a new row, because it it auto incremented. I will update the post with the database tables to make things more clear. — Ernst Reidinga, Oct 16 '18 at 20:14
What is your Unique key index? Insert query with on update requires a field which is unique. If you don't have a unique key other than the primary one, you need to provide value for the primary key. or you should create a unique index for which your insert query will check for duplication. Check the documentation here: https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/#description — Ahmad Faiyaz, Oct 16 '18 at 20:15
That is where the problem is - because the only unique key is the id. The programme_start/programme_end can't be unique because there are more programmes from different stations containing the same date/time. This is the same for the other fields.. A program can occure more then once on different times.. So i need to search in the table if there is a row with the same start/end/title/subtitle and IF THERE IS NOT i need to insert a new row. — Ernst Reidinga, Oct 16 '18 at 20:20

score 0 · Accepted Answer · answered Oct 16 '18 at 20:42

0

Try changing table key

You can set all columns which together should be unique, as the key.

For example TV Show T, Season S, Episode E, starts at Z and Ends at K, in year Y, whit station_id I

So this can be the key: (station_id ,programme_start , programme_end, title, episode, season, year)

I think this key also makes more sense logically.

answered Oct 16 '18 at 20:42

ali ghasemzad

66
1
10

How do i do that? I dont understand - sorry – Ernst Reidinga Oct 16 '18 at 20:44
i found this: `ALTER TABLE 'programmes' ADD UNIQUE 'unique_index'('station_id', 'programme_start', 'programme_end', 'title', 'subtitle', 'episode', 'season');` but i dont know what it does, or how to use it – Ernst Reidinga Oct 16 '18 at 20:50
Yes - I have about 1500 stations (tv stations) in the table stations. For each station i have 14 days of scheduling. This comes to about 250k programmes in the programmes table. Which all are unique, because there can be programmes at the same start/end but have a different station_id. So every row must be unique. - At this moment i do a select count first to check if the row exists, if not insert a new one. But it would be better and faster to do this in one sql query. – Ernst Reidinga Oct 16 '18 at 20:56
check this link, it can help https://stackoverflow.com/a/8859374/4798168 – ali ghasemzad Oct 16 '18 at 20:57
Okay and then i will be able to do a insert if not exists? – Ernst Reidinga Oct 16 '18 at 21:00
as you said, same program at the same time can be shown in different station_id so `station_id` must be a part of table key. Generally, all columns that must be unique together, should be considered as the key. – ali ghasemzad Oct 16 '18 at 21:01
Thank you. so `ALTER TABLE programmes DROP PRIMARY KEY, ADD PRIMARY KEY(station_id, programme_start, programme_end, title, subtitle, episode, season);` will make the row unique. – Ernst Reidinga Oct 16 '18 at 21:06
Then there would be no way to have duplicate rows! so you can check if the row does not exists, insert the new row. these links can also help: https://stackoverflow.com/a/3164741/4798168 -- https://stackoverflow.com/a/3164595/4798168 – ali ghasemzad Oct 16 '18 at 21:06
You're welcome:) Accept the answer if this is the solution. – ali ghasemzad Oct 16 '18 at 21:15
I did had to change the primary key to only the station_id, and programme_start and programme_end because the title field is a TEXT field (blob) which can't be primary because of the variable length. But the combination of station_id, start and stop time is unique enough because otherwise the programmes would overlap - so this works for me. – Ernst Reidinga Oct 16 '18 at 21:18

MySQL Insert prevent double

1 Answers1