如何在sql中实现自动搜索？ [英] How to implement a auto search in sql?

问题描述

大家好，



所以我有一个包含大约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.

推荐答案

他很大程度上依赖于你的数据，但你可以在你的标量中使用相关性选择。类似

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

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

Maciej Los at 10 m之前

Maciej Los at 10 mins ago

ingredient_id是主键吗？

Does ingredient_id is primary key?

会员11447944在4分钟前

Member 11447944 at 4 mins ago

是的。所以有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 PRIMARY KEY Constraint [ ^ ]

这篇关于如何在sql中实现自动搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！