0

Database: c_id, name (c_id is a primary key)

Context (made up context for this problem to make sense):

In this context we can assume no one has the same name. I own a hotel and a customer walks in. Her name is Madi Smith (before she got married, her name was Madi Andrews). I do not know if this is a new customer. Then, I do my process:

  1. first find_customer(name) which returns me a primary key or FALSE if not found.
  2. If I received a primary key then I always update_customer(c_id, name) otherwise if the customer was not found I insert_customer(name).

However, my hypothetical problem arises when I think like this.

  1. Process 1 | James walks in
  2. Process 1 | find_customer('james') -> return's FALSE
  3. Process 2 | James walks in (same person as above)
  4. Process 2 | find_customer('james') -> return's FALSE
  5. Process 1 | insert_customer('james')
  6. Process 2 | insert_customer('james')

Now I have a duplicate row of james's when they are the same person. How are you actually supposed to insert/update data?

Jackie
  • 372
  • 5
  • 16
  • Use [`INSERT ... ON DUPLICATE KEY UPDATE`](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html), no matter which insert hits first the second will just update with the same data. – Nick Jun 29 '20 at 02:18
  • Search for “upsert” for many duplicates of this. If there is only one ‘James’, it is the responsibility of the program to associate such (how to know it really is the same?). – user2864740 Jun 29 '20 at 02:19
  • @Nick Could you show me an example in my context for james? – Jackie Jun 29 '20 at 02:21
  • @Jackie there are many examples if you type `mysql upsert` into the search box at the top of the page – Nick Jun 29 '20 at 02:23
  • See for example the duplicate... – Nick Jun 29 '20 at 02:24
  • @Nick on duplicate key only helps you update some data if you have constraints preventing duplicates; here they want to prevent duplicates in the first place. This is not a duplicate of the other question. – ysth Jun 29 '20 at 02:58
  • but it is a duplicate of https://stackoverflow.com/questions/5412669/prevent-duplicate-sql-entries – ysth Jun 29 '20 at 03:03
  • @ysth agreed, there would need to be a unique key on `name`. But there are many other ways to do this as well. – Nick Jun 29 '20 at 03:04
  • @ysth i've added that to the duplicates list – Nick Jun 29 '20 at 03:06

1 Answers1

0

Consider the following:

  INSERT INTO <TABLE> ("james")
        SELECT "james"
  WHERE NOT EXISTS (SELECT * FROM <TABLE> WHERE name = "james" )

This will ensure that you are only inserting one row at a time and will not insert if another entry is found, whether process 1 inserted first or process 2 did.

Zmalski
  • 35
  • 6