为什么此查询不会使用查找更新字段？(Why won't this query update the field with the lookup?)

为什么此查询不会使用查找更新字段？(Why won't this query update the field with the lookup?)

我正在尝试运行以下查询以从另一个表更新一个表。 日期和电子邮件地址，工作和执行过，不过我用从参考表得到SUBJECT_1嵌套查询没有。 我究竟做错了什么？

SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup INNER JOIN PAD_ApplicantLost2000 ON tblSubjectLookup.Subj_Name=PAD_ApplicantLost2000.raw_subj_interest_1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000

源表，PAD_ApplicantLost2000，具有包含，例如，“生物学” A“的原始对象”列。 还有一个表，tblSubjectLookup，这对所有科目的代码，所以Subj.Name具有“生物”和“Subj_ClusName”有“B1”，这是需要我的目标表中去。 然而，在目标表中的字段SUBJECT_1不填充。

我究竟做错了什么？

I'm trying to run the following query to update one table from another. The dates and email address work and carry across, but the nested query I'm using to get Subject_1 from a reference table does not. What am I doing wrong?

SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup INNER JOIN PAD_ApplicantLost2000 ON tblSubjectLookup.Subj_Name=PAD_ApplicantLost2000.raw_subj_interest_1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000

The origin table, PAD_ApplicantLost2000, has a 'raw subject' column which contains, for example, 'Biology'. There is another table, tblSubjectLookup, which has codes for all subjects, so Subj.Name has 'Biology' and 'Subj_ClusName' has 'B1', which is what needs to go in my target table. However, the Subject_1 field in the target table does not populate.

What am I doing wrong?

最满意答案

为什么不直接使用WHERE ，而不是再次加入表条款。 我还添加了LIMIT 1只是为了确保只返回值1。

SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup WHERE tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1 LIMIT 1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000

另外，也许会更好，办法做到这纯粹是直接加入该表。

SELECT FirstRegistered As SignUpdate, Subj_ClusName AS Subject_1, Email_Address FROM PAD_ApplicantLost2000 LEFT JOIN tblSubjectLookup ON tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1

Why not just use a WHERE clause instead of joining the table again. I also added LIMIT 1 just to make sure only 1 value is returned.

SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup WHERE tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1 LIMIT 1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000

Another, possibly better, way to do it would just be to join the table directly.

SELECT FirstRegistered As SignUpdate, Subj_ClusName AS Subject_1, Email_Address FROM PAD_ApplicantLost2000 LEFT JOIN tblSubjectLookup ON tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1