0

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

philipxy
  • 14,867
  • 6
  • 39
  • 83
roxyhen
  • 11
  • 4
  • Does this answer your question? [Update with join with BigQuery](https://stackoverflow.com/questions/47373774/update-with-join-with-bigquery) – Paul T. May 20 '22 at 01:18
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) Please read the edit help re formats for code blocks. [mre] [ask] [Help] – philipxy Aug 05 '22 at 05:46
  • Debug questions a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Aug 05 '22 at 05:50

1 Answers1

0

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

Anjela B
  • 1,150
  • 1
  • 2
  • 7
  • I appreciate your help--I ran the query you suggested and received the following error message: "UPDATE/MERGE must match at most one source row for each target row" – roxyhen May 20 '22 at 15:38
  • This [SO post](https://stackoverflow.com/questions/72310307/bigquery-sql-update-and-join/72314858?noredirect=1#comment127766862_72314858) can help with your error. Can you share also how did you modify your code, both of table information and complete sample data? – Anjela B May 23 '22 at 00:29
  • my second table contained duplicates--I have removed them and the query was able to run, updating my first table. Thank you again for the help. – roxyhen May 24 '22 at 13:56