Click here to Skip to main content
15,943,477 members
Please Sign up or sign in to vote.
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
Updated 12-Feb-15 6:44am
v2
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?

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
 
Share this answer
 
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...
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[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900