CODEWITHSUNDEEP
google-bigquery
eilalan
eilalan

Reputation: 689

bigQuery assign a value to table 1 based on table 2

I would like to update Table 2 based based on Table 1 that is given by:

Row     sample_id   PIK3CA_features
1       huDBF9DD    chr3_3268035_CT  
2       huDBF9DD    chr3_3268043_AT  
3       huDBF9DD    chr3_3268049_T

Table 2:

Row     sample_id   chr3_3268035_CT  chr3_3268043_AT  chr3_3268049_C

1       huDBF9DD    1                1                null
2       huDBF9De    null             null             null
3       huDBF9Dw    null             null             null

For each row in Table 1, if its samle_id is correspondent in Table 2 then I'd like to update the respective PIK3CA_feature in Table 2 to 1.

How can I pass the sample_id and PIK3CA_features values from Table 1 as parameters to update Table 2 in a SQL command?

Upvotes: 1

Views: 132

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

You can use an UPDATE statement to accomplish this. Assuming I understand correctly, you want something like this query:

#standardSQL
UPDATE table2 AS t2
SET
  chr3_3268035_CT =
    IF(t1.PIK3CA_features = 'chr3_3268035_CT', 1, chr3_3268035_CT),
  chr3_3268043_AT =
    IF(t1.PIK3CA_features = 'chr3_3268043_AT', 1, chr3_3268043_AT),
  chr3_3268049_C =
    IF(t1.PIK3CA_features = 'chr3_3268049_C', 1, chr3_3268049_C)
FROM table1 AS t1
WHERE true;

This will set the appropriate column in table 2 to have a value of 1 based on the value of PIK3CA_features. If you have a lot of these columns, you can generate the query using Python or some other programming language, or you can generate all the column_name=expression pairs using a query:

#standardSQL
SELECT
  STRING_AGG(FORMAT('%s=IF(t1.PIK3CA_features="%s",1,%s)',
                    PIK3CA_features, PIK3CA_features, PIK3CA_features), ',\n')
FROM (
  SELECT DISTINCT PIK3CA_features
  FROM table1
);

This produces a list like:

chr3_3268035_CT=IF(t1.PIK3CA_features="chr3_3268035_CT",1,chr3_3268035_CT),
chr3_3268049_C=IF(t1.PIK3CA_features="chr3_3268049_C",1,chr3_3268049_C),
chr3_3268043_AT=IF(t1.PIK3CA_features="chr3_3268043_AT",1,chr3_3268043_AT)

Upvotes: 1

Related Questions