BigQuery - SQL UPDATE and JOIN-CodePudding

i have two tables. Table1 = dalio which is an event list with select customers. Table2 = master_list which is a master customer list from all past events.

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". All rows in the "id" column are currently blank. I only want the customer numbers where the "fullname" column in dalio & "name" column in master_list are an exact match.

This is what I have in BigQuery so far:

UPDATE lce-tess.Tess_Attributes.dalio

SET lce-tess.Tess_Attributes.dalio.fullname = lce-tess.Tess_Attributes.master_list.name

FROM lce-tess.Tess_Attributes.dalio

INNER JOIN lce-tess.Tess_Attributes.master_list

CAST(lce-tess.Tess_Attributes.master_list.customer_no AS STRING) = lce-tess.Tess_Attributes.dalio.id

WHERE lce-tess.Tess_Attributes.dalio.id IS NULL

Portion of each table below-- dalio (table1):

t2-masterlist:

You can try the query below:

UPDATE `t1-dalio` a
SET a.id = b.customer_no
FROM `t2-masterlist` b
WHERE a.fullname = b.name

Result t1-dalio: