Geek asks and answers

MySQL: How can I do a DUPLICATE KEY UPDATE where two primary keys should match?

I need to be able to run all of the following within a single call of a query using mysqli-> multi_query, so this is complicated. I have a table consisting of the following columns:

  • ID
  • Email
  • event_promo_code
  • event_id

When the script starts, I need to be able to insert new lines or an UPDATE line if both idAND event_idmatch the existing record (not just one key or the other).

Now I have:

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 - . event_id , , , , , BOTH .

+3
3

( )

UNIQUE (id, event_id). , :

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

(, DROP UNIQUE , ).

+4

REPLACE. id event_id:

REPLACE , INSERT, , , PRIMARY KEY UNIQUE index, . . 13.2.5, " INSERT".

+3
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';
0
source

More articles:


All Articles