Search code examples
sqljoingoogle-bigquerysql-update

BigQuery - SQL UPDATE and JOIN

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`
ON
  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): enter image description here
master_list (table2): enter image description here

Solution

  • t1-dalio:

    enter image description here

    t2-masterlist:

    enter image description here

    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:

    enter image description here