3

I need to be able to run all of the following within one query call using mysqli->multi_query, which is why this is tricky. I have a table that consists of the following columns:

  • id
  • email
  • event_promo_code
  • event_id

When the script is run, I need to be able to insert new rows or UPDATE a row if both the id AND event_id match an existing record (not just one key or the other).

What I have now is:

INSERT INTO `rsvps` 
SET id='$rsvpID', email='$rsvpEmail', 
      event_promo_code='$rsvpEventCode', event_id='$eventID' 
ON DUPLICATE KEY UPDATE id='$rsvpID', 
      email='$rsvpEmail', event_promo_code='$rsvpEventCode', event_id='$eventID';

id is my primary key. If I also set event_id as a key, it does an update when either id matches a record or event matches a record, but doesn't check to see if BOTH match at the same time before it updates.

kiriloff
  • 25,609
  • 37
  • 148
  • 229
user1418227
  • 201
  • 1
  • 4
  • 12
  • So what happens if only one key matches? You want the insert to fail with no update? – eggyal May 25 '12 at 20:50
  • How is `id` generated? Is `event_id` a foreign key column? – raina77ow May 25 '12 at 20:53
  • The idea is that the same id can be used multiple times as long as event_id's are different. So if event_id matches but id doesn't match, it would just do a regular insert. If id matches, but event_id doesn't match, it would also do a regular insert. – user1418227 May 25 '12 at 20:53
  • 1
    @user1418227: In that case, the only `UNIQUE KEY` on your table should be `(id, event_id)`. – eggyal May 25 '12 at 20:53
  • The admin will upload a CSV file with all of these columns pre filled out. id isn't auto_increment since it is defined by a third party who will create these CSV files – user1418227 May 25 '12 at 20:54
  • So I guess your table has a compound primary key, (id-event_id)? – raina77ow May 25 '12 at 20:54
  • I guess so, but how do I make that happen? Not a MySQL pro unfortunately. I'm using Sequel Pro mac client to do my structure/setup. – user1418227 May 25 '12 at 20:58

3 Answers3

4

(Upgrading to an answer)

The only UNIQUE constraint on your table should be defined over the combined columns (id, event_id). It sounds as though this should probably also be your primary key:

ALTER TABLE rsvps DROP PRIMARY KEY, ADD PRIMARY KEY (id, eventid);

(Obviously you will also need to DROP any other UNIQUE keys which have been defined).

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Awesome thanks! This worked and totally makes sense. It wasn't letting me do this (id, eventid) until I dropped the other key which is why I was having trouble before. Thanks again! – user1418227 May 25 '12 at 21:11
3

Consider using REPLACE command. For your case, create unique index from id and event_id:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Syntax”.

Aleš Kotnik
  • 2,654
  • 20
  • 17
  • Would that replace only if both id and event_id match the record at the same time? Because that's what I'm looking for. Not just replace if one of the two matches – user1418227 May 25 '12 at 20:57
  • Sort of. Replacement will take place if: `id` matches (primary key) or if `id` and `event_id` (unique index) matches but not if only `event_id` matches. If `id` + `event_id` match is the only desired case, consider creating creating composite primary key from `id` and `event_id` – Aleš Kotnik May 25 '12 at 21:03
0
INSERT INTO rsvps (id, email, event_promo_code, event_id)
 values('$rsvpID', '$rsvpEmail', '$rsvpEventCode', '$eventID')
 ON DUPLICATE KEY 
 UPDATE id='$rsvpID', email='$rsvpEmail', event_promo_code='$rsvpEventCode', event_id='$eventID';
Kyra
  • 5,129
  • 5
  • 35
  • 55