CODEWITHSUNDEEP
c#mysql.net
YosiFZ
YosiFZ

Reputation: 7890

MySql Insert if not exist

I want to add a row to my MySql table only if it's not exist in the table wi this command:

INSERT INTO client.viewcount (clientid, userid)
SELECT * FROM (SELECT '1111111', '222') AS tmp
WHERE NOT EXISTS (
    SELECT clientid,userid FROM client.viewcount WHERE clientid = '1111111' AND userid = '222'
) LIMIT 1;

It's work and insert only if it's not exist in the table. The issue i have another table

Users

id    userid    viewcount
1     123456    45
2     987741    62
3     741369    20

And i want to be able to increase the viewcount of a user. Something like : if i add

(clientid,userid) => (a432543,123456) 
If it's not exist in the table => Insert and increase the viewcount to 46.

Can i implement it in the one commend?

Upvotes: 1

Views: 1521

Answers (3)

Damith
Damith

Reputation: 63065

IF EXISTS(SELECT 1 FROM UserTable WHERE clientid = @clientid  AND userid = @userid )
BEGIN
    UPDATE ViewcountTable SET viewcount = viewcount+1
    WHERE clientid = @clientid  AND userid = @userid;
END
ELSE
BEGIN
      INSERT INTO UserTable (clientid, userid);
      INSERT INTO ViewcountTable (userid, 0);
END IF;

Upvotes: 3

Dr Schizo
Dr Schizo

Reputation: 4352

Not tried this so might be couple of errors (don't have tables to try this on) but should work.

DECLARE @viewcount INT;
SET @viewcount = 0;

IF NOT EXISTS (SELECT clientid,userid FROM client.viewcount WHERE clientid = @clientid AND userid = @userid)
BEGIN
    INSERT INTO client.viewcount (clientid, userid)
    VALUES (@clientid, @userid)

    SELECT @viewcount = viewcount FROM [Users] WHERE clientid = @clientid  AND userid = @userid
    SET @viewcount = @viewcount + 1;
    UPDATE [Users] SET viewcount = @viewcount
    WHERE clientid = @clientid  AND userid = @userid 
END

Upvotes: 0

nabuchodonossor
nabuchodonossor

Reputation: 2060

maybe the 'replace into' can help you: it inserts (or updates if the record already exists).

Upvotes: 0

Related Questions