I want to execute a DELETE statement at my BigQuery table based on the date. I am programming in Python and using the BigQuery library.
from google.cloud import bigquery
I have two tables in Bigquery (tableA and tableB). Both have a column date called "fecha"; Using this column and the date '2023-09-30,' I want to update the records of the tableA with the tableB.
First, for this I executed my Query DELETE:
sql_query = """ DELETE FROM {}.{}.{}Where date(fecha) = CAST('2023-09-30' AS DATE); """.format('project', 'dataset', 'tableA')
query_job = client.query(sql_query)
Then, I execute my query SELECT to my tableB.
query = """SELECT *FROM {}.{}.{}WHERE DATE(fecha) = CAST('2023-09-30' AS DATE)""".format('project', 'dataset', 'tableB')
query_job = client.query(query)
To the result of the SELECT, I apply an iteration to prepare it by applying a .isoformat () dates, replace the NAN by None and keeping all the Rows as DICT into a list to insert it into the tableA.
temporal_list = []
for row in query_job.result():
result_dict = {}
for key in row.keys():
if isinstance(row[key], datetime):
result_dict[key] = row[key].isoformat()
elif isinstance(row[key], float) and math.isnan(row[key]):
result_dict[key] = None
else:
result_dict[key] = row[key]
temporal_list.append(result_dict)
errors.append(client.insert_rows_json('project.dataset.tableA', temporal_list))
However, despite the fact that the SELECT and Insert work well, my Query DELETE no. It is executed apparently without problems and had previously been working well but now it is not removing anything. When I apply a SELECT query based on "fecha" column to tableA, it correctly returns the desired rows and works well, but not the DELETE query. Why this occur? Thanks
