英文:
Mysql query based on 2 tables
问题
我有2个名为new_test2和keywordssector的MySQL表格。
表格new_test2有字段title、noticeText和sectoradd。
而名为keywordssector的表格有2个字段keywords和sector。
我尝试运行下面提到的脚本，以在表格new_test2的title和noticeText字段中搜索在表格keywordssector的keywords字段中提到的单词，并将表格keywordssector中的sector字段中提到的部门代码（用逗号分隔）添加到表格new_test2中的sectoradd列中。
然而，我遇到了错误 - #1054 - 在'where子句'中找不到列'keywordssector.keywords'，即使列keywords在表格keywordssector中存在。我哪里搞错了？
UPDATE new_test2
SET sectoradd = CONCAT_WS(',', sectoradd, keywordssector.sector)
WHERE (
new_test2.title LIKE CONCAT('%', (SELECT keywords FROM keywordssector WHERE keywordssector.keywords IS NOT NULL), '%')
OR new_test2.noticeText LIKE CONCAT('%', (SELECT keywords FROM keywordssector WHERE keywordssector.keywords IS NOT NULL), '%')
) AND keywordssector.sector IS NOT NULL;
我尝试了另一种方法，但没有成功 - 同样的错误。
UPDATE new_test2
SET sectoradd = CONCAT_WS(',', sectoradd, sector)
WHERE (
new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%')
) AND keywordssector.sector IS NOT NULL
AND new_test2.sectoradd NOT LIKE CONCAT('%', sector, '%')
JOIN keywordssector ON (new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%'));
英文:
I have 2 mysql table named new_test2 and keywordssector.
Table new_test2 has the fields title, noticeText and sectoradd.
And table named keywordssector has 2 fields keywords and sector.
I am trying to run the below mentioned script to search for words mentioned in the field keywords (in table keywordssector) in the fields title and noticeText in the table new_test2 and add the sector/s codes (separated by commas) mentioned in sector of table keywordssector to column sectoradd mentioned in table new_test2.
However I am getting the error - #1054 - Unknown column 'keywordssector.keywords' in 'where clause'. Even though the column keywords is present in the table keywordssector. Where am I goofing up?
UPDATE new_test2
SET sectoradd = CONCAT_WS(',', sectoradd, keywordssector.sector)
WHERE (
new_test2.title LIKE CONCAT('%', (SELECT keywords FROM keywordssector WHERE keywordssector.keywords IS NOT NULL), '%')
OR new_test2.noticeText LIKE CONCAT('%', (SELECT keywords FROM keywordssector WHERE keywordssector.keywords IS NOT NULL), '%')
) AND keywordssector.sector IS NOT NULL;`
Another one I tried without success - same error
UPDATE new_test2
SET sectoradd = CONCAT_WS(',', sectoradd, sector)
WHERE (
new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%')
) AND keywordssector.sector IS NOT NULL
AND new_test2.sectoradd NOT LIKE CONCAT('%', sector, '%')
JOIN keywordssector ON (new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%'));
答案1
得分: 1
您在WHERE子句中引用了keywordssector表，但实际上没有将其与new_test2表进行连接。在您当前的查询中，keywordssector表未包含在FROM子句中，也没有以任何方式连接，这就是为什么MySQL无法识别keywordssector.keywords列的原因。
此查询应该可以正常工作：
UPDATE new_test2
JOIN keywordssector ON (
new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%')
)
SET new_test2.sectoradd = CONCAT_WS(',', new_test2.sectoradd, keywordssector.sector)
WHERE keywordssector.sector IS NOT NULL
AND new_test2.sectoradd NOT LIKE CONCAT('%', keywordssector.sector, '%');
英文:
You are referencing the keywordssector table in the WHERE clause without actually joining it with the new_test2 table. In your current query, the keywordssector table is not included in the FROM clause or joined in any way, which is why MySQL is unable to recognize the keywordssector.keywords column.
This query should be working properly:
UPDATE new_test2
JOIN keywordssector ON (
new_test2.title LIKE CONCAT('%', keywordssector.keywords, '%')
OR new_test2.noticeText LIKE CONCAT('%', keywordssector.keywords, '%')
)
SET new_test2.sectoradd = CONCAT_WS(',', new_test2.sectoradd, keywordssector.sector)
WHERE keywordssector.sector IS NOT NULL
AND new_test2.sectoradd NOT LIKE CONCAT('%', keywordssector.sector, '%');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论