Updating and Deleting Google BigQuery Data

To update or delete content, you must use PROC SQL to submit code to the Google BigQuery database. You must also ensure that the DBIDIRECTEXEC system option is enabled. DBIDIRECTEXEC is enabled by default for Google BigQuery.

The following example updates the value of the variable Price in table Stock23 when the variable Instock is greater than 50.

libname x1 bigquery project='Project1' 
                         schema='Region1';

proc sql;
   update x1.stock23 set price=price*1.1
      where instock > 50;
quit;

The following example deletes rows from table Mydata where the variable A is set to 1.

libname x1 bigquery project='Project1' 
                         schema='Region1';

proc sql;
   delete from x1.mydata where a=1;
quit;

This example deletes any row where the variable A is not specified (is missing). The database checks for null values.

libname x1 bigquery project='Project1' 
                         schema='Region1';

proc sql;
   delete from x1.mydata where a=.;
quit;