CODEWITHSUNDEEP
mysqlnode.js
Roee Gavirel
Roee Gavirel

Reputation: 19445

MySQL - select or insert

I have users from many external sources which I try to map to internal userId, so the table I have is:

userId, externalSourceId, externalUserId

In my code, I'm getting externalSourceId and externalUserId and want to get the userId from the database, if exists, otherwise, create one and return the newly created value. I need this action to be atomic because several processes may try to do the same thing at the same time, so I wished only the first time will create a userId.

In pseudo code it will look like that:

  1. u = find user with (externalSourceId, externalUserId)
  2. if no u:
    2.1. u = create new user with (externalSourceId, externalUserId) and random userId
  3. return u

Upvotes: 0

Views: 78

Answers (2)

Bhagyashree Sarkar
Bhagyashree Sarkar

Reputation: 519

INSERT IGNORE INTO test (externalSourceId,externalUserId) VALUES (23,32);

SELECT userId FROM test WHERE externalSourceId=23 AND externalUserId=32;

You can use this if externalSourceId and externalUserId are defined unique.

Upvotes: 1

AbhinavD
AbhinavD

Reputation: 7282

INSERT INTO `users`
(`externalSourceId`, externalUserId)
VALUES( 10, 100)
ON DUPLICATE KEY
UPDATE userId=userId

You can also use insert ignore. You can read more about DUPLICATE KEY versus INSERT IGNORE

Upvotes: 1

Related Questions