CODEWITHSUNDEEP
mysqlsql
dasdasd
dasdasd

Reputation: 2031

UPDATE or INSERT on the same query without unique value

Hello Im trying to find a way the UPDATE or INSERT data with one query.

I have a table with like this:

+------+-------------+
| User | action_type |
+------+-------------+
| Jon  | 1           |
| Kate | 2           |
| Jon  | 4           |
+------+-------------+

I want to insert new value for Jon only if there is no values of Jon.

If I have values of Jon I want to update all the rows with Jon.

Ive read about INSERT ... ON DUPLICATE KEY UPDATE but I dont have unique values.

Thanks for helping.

Upvotes: 0

Views: 78

Answers (2)

fancyPants
fancyPants

Reputation: 51878

Don't count like in user468891's answer. It might become a performance issue. Instead just check if an entry exists. As soon as an entry is found, the query returns true, count continues to find all records.

DELIMITER $$
CREATE PROCEDURE insertorupdate (IN name VARCHAR(20))
BEGIN
    IF (EXISTS(SELECT 1 FROM table WHERE User = name)) THEN
        // UPDATE...
    ELSE
        // INSERT...
    END IF;
END $$
DELIMITER ;

Upvotes: 0

user468891
user468891

Reputation: 321

you can count the entries for jon. If exists update else insert. if you want to implement only using sql you can use an stored procedure

something like this: 

CREATE PROCEDURE insertorupdate (IN name VARCHAR(20))
BEGIN
    DECLARE numJon  INT;
    SELECT COUNT(*) INTO numJon FROM table WHERE User=name;
    IF numJon > 0 THEN
        // UPDATE ;
    ELSE
      // INSERT
    END IF;

END

Then you can call you Store Procedure:

CALL insertorupdate('John');

If you can do it from your app you can call the same thing but separatelly. Do a select count, test if count if grater than 0 and then do the insert or the update on DB

Upvotes: 1

Related Questions