我正在努力使我的SQL更加优雅和/或减少需要运行的查询数量。我有两个问题:
- 第一个查询会影响我的原始表,更新缩写带有描述记录中的信息的记录
- 第二个查询基于缩写记录
有没有办法把这些写成一个单独的查询
简化查询1:
UPDATE [tblManipulateD]
SET [tblManipulateD].[abbreviation] = RIGHT([tblManipulateD].[description],3)
WHERE [tblManipulateD].[description] LIKE “*abbrev:*";
简化查询2
UPDATE [tblManipualteD]
LEFT JOIN [tblStorage] ON ([tblManipulateD].[abbreviation] = [tblStorage].[storage abbreviation]
SET [tblManipualteD].[product code] = [tblStorage].[storage product]
WHERE [tblManipulateD].[description] LIKE “*abbrev:*";
同样,我想知道是否有一种方法(在一个查询中)可以根据主表的计算将辅助表连接到主表。我感谢你的帮助和/或洞察力!
是的,您可以加入描述列的最后3个字符:
UPDATE [tblManipulateD]
SET [tblManipulateD].[abbreviation] = RIGHT([tblManipulateD].[description],3),
[tblManipualteD].[product code] = [tblStorage].[storage product]
FROM [tblManipulateD]
LEFT JOIN [tblStorage] ON RIGHT([tblManipulateD].[description],3) = [tblStorage].[storage abbreviation]
WHERE [tblManipulateD].[description] LIKE “*abbrev:*";