CODEWITHSUNDEEP
phpmysqlsql
switz
switz

Reputation: 25188

MYSQL ON DUPLICATE KEY UPDATE question

I'm a little bit confused as to what ON DUPLICATE KEY UPDATE does. What I'm looking for is something that will check an INSERT INTO SQL command and if any row is a duplicate do not update that row. Now if anything in that row is not a duplicate (but some is), I'd like to replace that row with the updated information.

Is this possible using basic MYSQL or am I going to have to pull all the data first, then cross check it. I'd rather not do that as all I'm trying to do is cache a decent amount of data once a day.

"INSERT INTO years (date,year,venue,city,state,country,showid) VALUES (?,?,?,?,?,?,?)"

Upvotes: 1

Views: 3266

Answers (5)

prodigitalson
prodigitalson

Reputation: 60413

ON DUPLICATE KEY UPDATE simply performs the SET statements you provide to it in the case of a duplicate key. It does not compare individual column values and only update the differing ones. It does sound like it will work for what you want to do as long as you have the proper column(s) defined as UNIQUE KEY or PRIMARY KEY.

However, what I normally do is run the insert and then catch the error and perform a differing action if I need to. This has the down side of issuing 2 queries if there is a duplicate but in my opinion it's much more maintainable.

Example:

$db = new PDO($dsn, $user, $pass);
$stmt = $db->prepare('INSERT INTO some_tbl (col1,col2,col3) VALUES (?,?,?)');
$values = array('Col 1 value','Col 2 Value', 'Col 3 Value');
try {
  $db->execute($values);
} catch (PDOException $e) {
  if($e->getCode() == 23000){
    // dupe key do some other action whether update or otherwise
  } else {
    // rethrow non dupe errors
    throw $e;
  }
}

Upvotes: 3

Aurimas
Aurimas

Reputation: 2493

As some people already suggested, the first thing you need to do is to define what is a duplicate row. That is done with setting a UNIQUE index. If, for example, you considered that there could be no duplicate venues in the table, you would set a UNIQUE index on venues. If it was the combination of a venue and a date (essentially saying - you cannot have two events at the same place on the same date), then you would define a composite UNIQUE index, which would look like UNIQUE(venue, date).

When you have such a set-up. you can start using ON DUPLICATE KEY UPDATE: if the data you are entering will match the existing composite unique key - you will only update the relevant columns. If not - you will add a new one. The syntax for the statement is:

INSERT INTO table (col1, col2, col3) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE SET col3 = VALUE(col3);

This would insert a new row if there was no unique key like that before (let's say the unique key is UNIQUE(col1, col2) - so there was no pair of col1 and col2 before). If a pair of col1 and col2 exists in the table, it will replace col3 value with the value you provided.

Now, when it comes to your example, it looks like you need a composite UNIQUE index on all of the columns. I am no expert, but for me that does not look like the best practice :)

Thus, I would suggest to rethink your table structure a bit:

  • Have a venues table, with columns something like "state, city, venue, venue_id". This table could have a UNIQUE index (state, venue, city).
  • Have a "main" table, with columns like "show_id, artist_id, venue_id, time ..". This table then would have a composite UNIQUE index (show_id, venue_id).
  • Have a artists table, with columns something like "artist_id, artist_name, etc*".
  • All three tables would have PRIMARY keys (which is basically UNIQUE and INDEX key at the same time) on the respective ids: show_id, venue_id, artist_id.

Upvotes: 1

bash-
bash-

Reputation: 6304

I misunderstood your question before, I edited this post

You would have to separate it into an INSERT statement and an UPDATE statement as you are trying to do 2 different things. And for the UPDATE statement to work, you at least NEED a key, and then check that row to see if any values are different.

You can check the existence of a row and insert only if it does not exist like this.

INSERT Statement

INSERT INTO tbl_name (key, col1, col2, col3) 
SELECT keyval, val1, val2, val3 
FROM dual
WHERE NOT EXISTS
(SELECT key, col1, col2, col3 
FROM tbl_name 
WHERE key = keyval AND col1 = val1 AND col2 = val2 AND col3 = val2)

dual is only a placeholder table that lets you insert whatever value you wish and allow a WHERE clause

UPDATE Statement

UPDATE tbl_name
SET col1 = val1
    col2 = val2
    col3 = val3
WHERE key = keyval AND 
(col1 <> val1 OR col2 <> val2 OR col3 <> val3)

Upvotes: 0

james_bond
james_bond

Reputation: 6908

From the official docs:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Upvotes: 0

Edgar Velasquez Lim
Edgar Velasquez Lim

Reputation: 2446

You can create a UNIQUE INDEX on every column of your data. This'll make it so it'll throw a duplicate error if all the columns of the new row is an exact duplicate of all the columns in an existing row, which is what you want.

ALTER TABLE TABLE ADD UNIQUE uniqueconstraintname(col1,col2,...);

W3Schools on Unique Constraints: http://www.w3schools.com/sql/sql_unique.asp

Upvotes: 0

Related Questions