通过从关系表中的主表中查找文本来更新关系 ID col
我将软件 PAD 文件数据库从 http://paddatabase.net/download.html 导入到 Microsoft Access 中一个名为 main 的表：
MAIN
-----
ID
ProgramName
Program_category_Class
CategoryID <- I created this to create a one to many relation with a new table Category
.
.
我创建了两个新表：类别和子类别。
Category
--------
ID
CategoryName
我将 :: 分隔符左侧的 Program_Category_Class 字段中的所有值提取到表类别中。 Program_Category_Class 中的数据如下所示：
Program_Category_Class
Business::Accounting & Finance
Games & Entertainment::Action
Business::Accounting & Finance
Business::Databases & Tools
我想要做的是创建一个 SQL 查询，该查询使用与 Program_Category_Class 上的 LIKE（左）匹配的类别表中的类别 ID 更新 MAIN 表。然后，我将修改子类别表的左右匹配查询。
我尝试了类似的操作，但没有得到结果：
UPDATE Main SET Main.category = (SELECT Category.ID
FROM Category
WHERE Category.CategoryName LIKE Main.Program_Category_Class+'%')
WHERE (((Exists (SELECT Category.ID
FROM Category
WHERE Category.CategoryName LIKE Main.Program_Category_Class+'%'))<>False));
由于它是一个公共数据库，我可以向任何感兴趣的人提供数据库的副本。
任何帮助将不胜感激。谢谢。
I imported the Software PAD File Database from http://paddatabase.net/download.html into Microsoft Access in a table called main:
MAIN
-----
ID
ProgramName
Program_category_Class
CategoryID <- I created this to create a one to many relation with a new table Category
.
.
I created two new tables: category and subcategory.
Category
--------
ID
CategoryName
I extracted all the values from the field Program_Category_Class on the left side of the :: delimiter into the Table Category. Data from Program_Category_Class looks like this:
Program_Category_Class
Business::Accounting & Finance
Games & Entertainment::Action
Business::Accounting & Finance
Business::Databases & Tools
What I want to do is create a SQL Query which Updates the MAIN table with the Category ID from the Category table that is a LIKE (Left) match on Program_Category_Class. I will then modify the query for a right left match for the subcategory table.
I tried something like this but get no result:
UPDATE Main SET Main.category = (SELECT Category.ID
FROM Category
WHERE Category.CategoryName LIKE Main.Program_Category_Class+'%')
WHERE (((Exists (SELECT Category.ID
FROM Category
WHERE Category.CategoryName LIKE Main.Program_Category_Class+'%'))<>False));
As it is a public DB I can provide a copy of the database to anyone interested.
Any assistance would be most appreciated. Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可能希望删除
<>False以及一些额外的括号。这不是您真正的问题，但它将有助于提高可读性。不，发生的情况是您的 like 谓词实际上是向后的 - 您试图将较长问题与较短答案进行比较。将 where 子句切换为WHERE Main.Program_Category_Class like Category.CategoryName + '%'并查看是否有帮助。此外，当新字段看起来名为
Main.CategoryID时，您正尝试更新Main.category。为了供将来参考，请考虑提供这些常见的有用的后续信息： 那么，您没有得到结果是什么意思？查询是否抱怨它无效（如果字段名称有问题，它应该如此），或者它没有更新任何行？
You may wish to remove
<>False, as well as some of the extra parenthesis. That's not your real problem, but it will help with readability. No, what's happening is that your like predicate is effectively backwards - you're trying to compare the longer question with the shorter answer. Switch the where clause toWHERE Main.Program_Category_Class like Category.CategoryName + '%'and see if that helps.Also, you are trying to update
Main.category, when the new field looks to be namedMain.CategoryID.For future reference, please consider these common helpful followups to provide: So what do you mean you get no results? Does the query complain that it's not valid (which it should, if the field names are an issue), or does it not updated any rows?