0

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
Roee Gavirel
  • 18,955
  • 12
  • 67
  • 94

2 Answers2

1
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

AbhinavD
  • 6,892
  • 5
  • 30
  • 40
  • The thing is that on "duplicate" I want to return the original userId, not update it. – Roee Gavirel May 04 '18 at 10:33
  • This would be the same `userId` that already exists. So yes, there would be one db update request. – AbhinavD May 04 '18 at 10:35
  • But I still not "getting" it outside. I can do it with two SQL queries. But I'm going to have many "gets" and few "creates" so it seems a big overhead. – Roee Gavirel May 04 '18 at 10:46
  • 1
    AFAIK, if you are using raw queries, result of `insert` itself is boolean and does not return the inserted object. If you are using ORM, I would look if that ORM supports this. For example, Sequelize supports [findOrCreate](http://docs.sequelizejs.com/manual/tutorial/models-usage.html#-findorcreate-search-for-a-specific-element-or-create-it-if-not-available) – AbhinavD May 04 '18 at 11:05
1

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.

Bhagyashree Sarkar
  • 509
  • 3
  • 12