0

Let's say I have this table

ID  |   Name    |   Hobby
---------------------------
1   |   Alex    |   fishing
2   |   Alex    |   soccer
3   |   Nick    |   bike
4   |   George  |   hike

ID - is unique. Hobby - is NOT unique (need to keep it as non-unique)

Inserting a record:

INSERT INTO my_table (ID, Name, Hobby) VALUES ('5', 'Christina', 'bike')

How to modify the query, if I need to insert the record if bike value does not exist at all in Hobby column?

Anotherwords:

  • VALUES ('5', 'Christina', 'bike') - would NOT be inserted as 3 | Nick | bike exists
  • VALUES ('5', 'Christina', 'cooking') would be inserted as cooking is not present in Hobby column at all.

Having existing database with thousands of records, there is a risk that there are duplicates already in Hobby...

But from now on.. when adding new records, I want to avoid adding if already exists..

Thank you.

ihtus
  • 2,241
  • 9
  • 34
  • 53
  • `where not exists (Select * from my_table B where hobby = 'bike' and A.hobby=b.hobby)`. and alias the outer my_table as A. – xQbert Aug 09 '17 at 22:10
  • Um, that's what UNIQUE does?!?!?!?!? But you can emulate this odd behaviour with a join – Strawberry Aug 10 '17 at 00:36
  • @xQbert: is it possible to do that with `VALUES` instead of select? – ihtus Aug 10 '17 at 04:37
  • @Strawberry could you give a concrete example? – ihtus Aug 10 '17 at 04:38
  • Perhaps. If you provided CREATE and INSERT statements (https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Aug 10 '17 at 07:31

2 Answers2

1

The easiest solution could be changing hobby column to unique. This way you will force your database to only insert unique hobbies. Another solution could be using triggers fore before insert / update.

Noob
  • 629
  • 5
  • 24
  • @ihtus Then the solution could be using triggers. I used triggers for checking on duplicates before inserting and before updating. – Noob Aug 09 '17 at 22:17
  • I expected another more elegant solution.. anything without triggers? – ihtus Aug 09 '17 at 22:18
  • @ihtus could you explain why you do not want to use triggers ? – Noob Aug 09 '17 at 22:20
  • @Mjh: To project on the simple DB example here... I have an existing DB (thousands record) and there is a risk that I have dublicates in Hobby.. But from now on.. I need to alter it considering that I won't add duplicates – ihtus Aug 10 '17 at 15:38
  • @ihtus if you do not want any duplicates anymore you have to use triggers. This will prevent the database to insert duplicates from insert and updates. Still have no idea why you do not want to use triggers. – Noob Aug 10 '17 at 15:43
-2

Based on MySQL: Insert record if not exists in table

But with some corrections (to fix the duplicate errors)

The following query works for me.

INSERT INTO my_table (ID, Name, Hobby)
SELECT * FROM (SELECT '5' AS ID, 'Christina' AS Name, 'cooking'  AS Hobby) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE Hobby= 'cooking'
) LIMIT 1;
ihtus
  • 2,241
  • 9
  • 34
  • 53
  • 1
    This is incorrect. You're prone to concurrency. Another process can insert the same row. The only way for your desired feature to work is to place a unique constraint over several columns. Since your columns are text-based, the proper solution is to use a trigger, concatenate textual values, hash them and insert the hash along with the data. Place the `unique` constraint over that one column and you won't have issues. Or, you can use this concurrency-prone falsy solution. – Mjh Aug 10 '17 at 15:40
  • @Mjh: To project on the simple DB example here... I have an existing DB (thousands record) and there is a risk that I have dublicates in Hobby.. But from now on.. I need to alter it considering that I won't add duplicates any more.. what is unclear here? – ihtus Aug 10 '17 at 15:41
  • @Mjh: I am ok with the above query, I don't have any other processed that will alter the table – ihtus Aug 10 '17 at 15:45