How to implement a auto search in sql?

Question

0.00/5 (No votes)

See more:

Hello everybody,

so i have a database with about 40 thousands of names.
There are similar names like "WATER" and "WATER(AQUA)" but they are treated as two separate products.
For example: WATER has id of 5, but "WATER(AQUA)" ingredient has a NULL id because of the added AQUA)
So basically i need to update WATER(AQUA) id to WATER id
the way i do it right now is

SQL

UPDATE ingredient_aliases
SET a.ingredient_id = 
 (SELECT MAX(ingredient_id) AS max_id
 FROM ingredient_aliases
 WHERE name LIKE '%WATER%'
 AND ingredient_id IS NOT NULL)
WHERE name LIKE '%WATER%' 
 AND ingredient_id IS NULL

But as you can see, i have to type WATER or other name for this product. It is very painful when you have a lot of names that are similar and you have to do it manually.
So here is the question:

can i somehow implement some sort of while statement that will automatically search for all NON-NULL Ingredients and their similar names and then will update all found similar names to the NON-NULL ingredient Id.

Posted 12-Feb-15 6:21am

Member 11447944

Updated 12-Feb-15 6:44am

Wendelius

v2

Add a Solution

Comments

Maciej Los 12-Feb-15 13:36pm

Does ingredient_id is primary key?

Member 11447944 12-Feb-15 13:42pm

yes. so there are NON-NULL ingredient_id and NULL ingredient_id names. So i want to update all similar names with NULL ingredient_id to the NON-NULL ingredient_id. I can do it with my code, just to type WATER. But im wondering is there a way that instead of manually typing the names instead of 'WATER' this code will do it by itself?

2 solutions

Solution 2

Maciej Los at 10 mins ago

Does ingredient_id is primary key?

Member 11447944 at 4 mins ago

yes. so there are NON-NULL ingredient_id and NULL ingredient_id names. So i want to update all similar names with NULL ingredient_id to the NON-NULL ingredient_id. I can do it with my code, just to type WATER. But im wondering is there a way that instead of manually typing the names instead of 'WATER' this code will do it by itself?

You can not set two identical primary keys! The PRIMARY KEY constraint uniquely identifies each record in a database table.

For further information, please see: SQL PRIMARY KEY Constraint[^]

Posted 12-Feb-15 7:53am

Maciej Los

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-02-12T06:47:00

Solution 1

his depends highly on your data but you can use correlation in your scalar select. Something like

SQL

UPDATE ingredient_aliases
SET a.ingredient_id =
 (SELECT MAX(ingredient_id) AS max_id
 FROM ingredient_aliases
 WHERE name LIKE '%' + ingredient_aliases.name + '%'
 AND ingredient_id IS NOT NULL)
WHERE ingredient_id IS NULL

Posted 12-Feb-15 6:47am

Wendelius

Comments

Member 11447944 12-Feb-15 13:07pm

i don't know why, but it gives me this error
13:05:37 Kernel error: ERROR: operator does not exist: unknown + text

Wendelius 12-Feb-15 15:06pm

What is the database you're using? MySQL, Oracle, Sql Server...

How to implement a auto search in sql?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to implement a auto search in sql?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us