问题描述
大家好，
所以我有一个包含大约40,000个名字的数据库。
有类似的名字，比如WATER和水（AQUA）但它们被视为两个独立的产品。
例如：WATER的id为5，但是WATER（AQUA）成分因为添加了AQUA而具有NULL id
所以基本上我需要更新WATER （AQUA）id to WATER id
我现在这样做的方式是
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
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
但是正如您所看到的，我必须为此产品键入WATER或其他名称。当你有很多相似的名字并且必须手动完成时，这是非常痛苦的。
所以这是一个问题：
我可以以某种方式实现某种while语句，它会自动搜索所有NON-NULL成分和它们的名字相似，然后将所有找到的类似名称更新为NON-NULL成分Id。
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.
推荐答案
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
ingredient_id是主键吗？
Does ingredient_id is primary key?
是的。所以有NON-NULL ingredient_id和NULL ingredient_id名称。所以我想用NULL ingredient_id将所有相似的名称更新为NON-NULL ingredient_id。我可以用我的代码来做，只需键入WATER。但我想知道是否有一种方法，而不是手动键入名称而不是'WATER'这个代码将自己做？
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?
PRIMARY KEY约束唯一标识数据库表中的每条记录。
有关详细信息，请参阅： []
这篇关于如何在sql中实现自动搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！