0

I am trying to insert the new records in table, I have two columns

      user_records
 ------------------------
| Attribute |  Datatype  |
|------------------------|
| user_id   | varchar(25)|
| count     | int        |
 ------------------------

Query :

INSERT into user_records Values("nix_1", 0)

but if the user already exists, then it should increase count by 1

Nixi
  • 7
  • 5

2 Answers2

0

You would have check if the record exists before attempting to INSERT. Your query should be something in line with below. Example works in SQL Server.

DECLARE @sUserID VARCHAR(25) = "nix_1";    // This should be the parameter passed on into the stored procedure

IF EXISTS(SELECT user_id FROM WHERE user_id = @sUserID)
    // Increase the count
    UPDATE user_records SET count = count  + 1 
        WHERE user_id = @sUserID
ELSE
    // Insert new record
    INSERT INTO user_records Values (@sUserID , 0)

Please refer to the below links for info on EXIST

EXISTS (Transact-SQL)

SQL EXISTS Operator

Difference between EXISTS and IN in SQL?

Community
  • 1
  • 1
fujiFX
  • 413
  • 9
  • 18
0

Why not use the INSERT ... ON DUPLICATE KEY syntax?

INSERT INTO user_records (user_id, count) VALUES ('nix_1', 0)
ON DUPLICATE KEY UPDATE count = count + 1;

http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Nick
  • 7,103
  • 2
  • 21
  • 43
  • I tried `INSERT INTO user_records VALUES('nix_1', 0) ON DUPLICATE KEY UPDATE count = count+1` but it gives error `Unrecognized keyword (near ON)`, `Unrecognized keyword (near DUPLICATE)`, `Unrecognized keyword (Near Key)`. – Nixi Jun 29 '16 at 23:36
  • Which version of MySQL and which data engine? – Nick Jun 29 '16 at 23:41
  • I am using xampp with sql version `Database client version: libmysql - mysqlnd 5.0.12-dev` and XAMPP `Version 3.22` – Nixi Jun 29 '16 at 23:46