0

I am trying to do an INSERT only if the combination of two columns (a and b) does not exist already. Otherwise, I want to do an UPDATE. The issue of the following code is that it always INSERTs a new row instead of updating when I want to. The reason I think, is because I don't manage do have a kind of two-unique-column in the settings of my table. Does any one have a solution? Google doesn't seem to be my friend today..

The table:

id : int, primary,  AI
a b c and d : int

The code:

$req = $connexion -> prepare("
INSERT INTO position (a,b,c,d)
VALUES (:a,:b,:c,:d)
ON DUPLICATE KEY UPDATE
c=:c;");
$position->bindParam(':a', $a);
$position->bindParam(':b', $b);
$position->bindParam(':c', $c);
$position->bindParam(':d', $d);
$a = $val_a;
$b = $val_b;
$c = $val_c;
$d = $val_d;
$req -> execute();
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
fazac
  • 25
  • 4
  • a b c d none of these are columns in your database table have a UNIQUE index. ID is the primary key, so it will work with duplicate ids. You need to add a UNIQUE INDEX on one of these columns to get it working. – Ananth Oct 25 '14 at 18:31
  • 1
    Add a unique key - `ALTER TABLE position ADD UNIQUE KEY (a,b)` – Sean Oct 25 '14 at 18:33
  • @Sean Post it as an answer. – Barmar Oct 25 '14 at 18:33

2 Answers2

1

Have you already tried to alter the table to reflect what you need? try this before executing your code.

ALTER TABLE `position` ADD UNIQUE `unique_index`(`a`, `b`);
Dave Plug
  • 1,068
  • 1
  • 11
  • 22
1

ON DUPLICATE KEY requires a UNIQUE KEY if you are not matching to the PRIMARY KEY. You can add a UNIQUE KEY by using an ALTER TABLE query

ALTER TABLE position ADD UNIQUE KEY (a,b)
Sean
  • 12,443
  • 3
  • 29
  • 47