Update if row exists, otherwise insert new row into another table

Question

I know this question but it didn't help.

I have a list with ids and values. Now I have to check if the id exists in my user database. If it does, then the row will be updated, otherwise I have to insert the id in another table (tmp_user).

Edit: Here is my try

IF NOT EXISTS (SELECT * FROM `wcf1_user` WHERE `steamID` = 1) THEN

INSERT INTO `wcf1_points_tmp` (`steamID`, `points`) VALUES (1, 2)

ELSE

// Update stuff......

END IF;

Result: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM wcf1_user WHERE steamID = 1) THEN INSERT IN' at line 1

Thanks for your help. :)

What does "it didn't help" exactly mean? Is your id column defined as primary key BTW? — Havelock, May 11 '14 at 08:19

score 1 · Accepted Answer · answered May 11 '14 at 09:56

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;

score 0 · Answer 2 · answered May 13 '14 at 07:21

This is domain logic, which doesn't belong in the data tier. Do it in PHP instead:

<?php
  // connect to the database
  $DSN = "mysql:dbname=$dbname;charset=utf8";
  $opt = array(PDO::MYSQL_ATTR_FOUND_ROWS => true);
  $dbh = new PDO($DSN, $username, $password, $opt);
  $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // one assumes you want to perform this operation atomically
  $dbh->beginTransaction();

  // attempt to update the row
  $update = $dbh->prepare('
    UPDATE wcf1_user
    SET    ......
    WHERE  steamID = ?
  ');
  $update->execute(array($steamID));

  // if the update didn't affect any rows
  if (!$update->rowCount()) {
    // insert into another table instead
    $insert = $dbh->prepare('
      INSERT INTO wcf1_points_tmp
        (steamID, points)
      VALUES
        (?, ?)
    ');
    $insert->execute(array($steamID, $points));
  }

  // tada!
  $dbh->commit();
?>

score -1 · Answer 3 · answered May 13 '14 at 07:26

-1

I think you are missing semicolon at the end of insert statement, try following,

IF NOT EXISTS (SELECT * FROM `wcf1_user` WHERE `steamID` = 1) THEN

INSERT INTO `wcf1_points_tmp` (`steamID`, `points`) VALUES (1, 2);

ELSE

// Update stuff...... remember to add semicolon at the end

END IF;

answered May 13 '14 at 07:26

AK47

3,707
3
17
36

-1 This will still give rise to the same syntax error that the OP originally cited: [`IF`](http://dev.mysql.com/doc/en/if.html) is a flow control statement that is only valid in stored programs. – eggyal May 13 '14 at 07:31
@eggyal, Oh! i see, I am not php or mysql programmer , so I trust you for this. – AK47 May 13 '14 at 07:32

Update if row exists, otherwise insert new row into another table

3 Answers3

Linked