如何在BigQuery中通过键值表替换数据表的Fruit字段值？

Got it, let's work through this issue step by step. First, let's break down why your original SQL is failing, then fix it to get exactly the result you want.

What's Wrong with Your Original Query? #

There are three key issues here:

• Syntax errors: You have extra commas in SELECT T1.*, T2.*, FROM and FROM [Table1] T1, JOIN — these break the SQL parser immediately.
• Incorrect join condition: You're joining on T1.Fruit = T2.Fruit, but T2.Fruit is the numeric code, which maps to T1.Answer_Code, not T1.Fruit. That's why your logic wasn't matching the right names.
• Legacy SQL restrictions: The error message references limitations of BigQuery's old Legacy SQL (the one using square brackets [Table1]), which has strict rules for JOIN conditions. We should use Standard SQL instead (it's BigQuery's default now and way more flexible).

Correct Query to Get Your Desired Result #

Since you can't use UPDATE SET, we'll create a SELECT query that returns the mapped data directly (you can even save this as a view for repeated use):

-- Use Standard SQL (BigQuery's default)
SELECT
  T2.Participant,
  T1.Fruit AS Fruit -- Matches your desired output column name
FROM
  `your-project.your-dataset.Table2` T2 -- Replace with your actual project/dataset/table path
JOIN
  `your-project.your-dataset.Table1` T1 -- Replace with your actual project/dataset/table path
ON
  T2.Fruit = T1.Answer_Code; -- Correct mapping between numeric code and fruit name

How This Works #

• We start with Table2 (your data collection table) because we want to preserve all its Participant records.
• We JOIN it to Table1 (the key-value table) using the correct relationship: T2.Fruit (the numeric code) matches T1.Answer_Code.
• The output will exactly match your desired structure (note: your example expected Ccc to map to Watermelon, but based on your data, Ccc has code 3 which maps to Pear — that's likely a typo in your expected result!):

  Participant|Fruit
  Aaa |Apple
  Bbb |Orange
  Ccc |Pear
  

Bonus: Handle Missing Codes #

If some participants have codes that don't exist in Table1, use LEFT JOIN instead of JOIN to keep those records (you can even replace NULL with a fallback value like 'Unknown'):

SELECT
  T2.Participant,
  COALESCE(T1.Fruit, 'Unknown') AS Fruit -- Replace NULL with a default if needed
FROM
  `your-project.your-dataset.Table2` T2
LEFT JOIN
  `your-project.your-dataset.Table1` T1
ON
  T2.Fruit = T1.Answer_Code;

内容的提问来源于stack exchange，提问作者cimmeria luz