CODEWITHSUNDEEP
sqlmergegoogle-bigquerydelete-row
franco_b
franco_b

Reputation: 878

BigQuery SQL delete rows from a target table based on a condition

In BigQuery I need to delete all rows (one or more) in table A and insert one rows from table B only if value in table B is not null

Table A:

dateVal
2021-02-01
2021-02-10

Table B:

dataValNew
2021-02-05

Desiderated output:

Table A:

dateVal
2021-02-05

If table B is

dataValNew
null

Desiderated output is (nothing change):

Table A:

dateVal
2021-02-01
2021-02-10

I try using Merge. Something like:

MERGE A as t
USING B as s
ON FALSE
WHEN NOT MATCHED BY SOURCE AND dataValNew is not null THEN
  DELETE
WHEN NOT MATCHED AND dataValNew is not null THEN
INSERT ROW

but dataValNew is not recognized because it's a field from source table.

Upvotes: 2

Views: 700

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10172

Probably you'll have to resort to scripting:

DECLARE cnt INT64 DEFAULT 0;
SET cnt = (SELECT COUNT(*) FROM B WHERE dataValNew is not NULL);
IF cnt > 0 THEN
  CREATE OR REPLACE TABLE A AS SELECT dataValNew as dataVal FROM B;
END IF;

Upvotes: 1

Related Questions