SQLTeam.com | Weblogs | Forums

BigQuery Ambiguous Column Name

See below--not sure why I keep getting the ambiguous column name error when all column have unique names:

UPDATE lce-tess.Tess_Attributes.dalio

SET fullname = t2.name

FROM lce-tess.Tess_Attributes.dalio t1

JOIN lce-tess.Tess_Attributes.master_list t2 ON cast(t2.customer_no as STRING) = t1.id

WHERE id IS NULL

Column to be set has no alias of table it belongs to

WHERE id IS NULL

So which table does 'id' come from? Also - in your construct you logically have 3 tables...you have 'lce-tess.Tess_Attributes.dalio' - and you have t1 and t2.

UPDATE t1
   SET fullname = t2.name
  FROM lce-tess.Tess_Attributes.dalio         t1
  JOIN lce-tess.Tess_Attributes.master_list   t2 ON cast(t2.customer_no as STRING) = t1.id
 WHERE t1.id IS NULL;

Of course - this may not work in your version of SQL. This is for Microsoft SQL Server (T-SQL) and it appears you are using something different. There is no STRING data type in SQL Server.

Hi, i have two tables. Table1 = dalio which is an event list with select customers. Table2 = master_list which is a master customer list.

dalio has an id column that needs to be filled in with customer numbers, which can be pulled from master_list column called customer_no. I only want the customer numbers where fullname in dalio & name in master_list are an exact match.

Here is what I have now in BigQuery:

Your help is greatly appreciated!