0

I want to update a target table (target) according to rows in a source table (source). For that, I use the MERGE statement. However, I would like the whole MERGE operation fails on some conditions, but cannot figure how to do.

Example

The target table contains :

id
==
1
2

The source table contains :

id | operation
==============
3  | ADD
4  | DELETE

Expected target table after MERGE (I don't want any update here because 4 corresponds to DELETE operation, and since there is no 4 line in target table, this is considered as an error and MERGE should fail atomically) :

id
==
1
2

As of now, I use the following request :

MERGE `target` target
USING `source` source
ON target.id = source.id
WHEN not matched AND source.operation = "ADD" THEN
  INSERT (id)
  VALUES (source.id)

But obviously, I got :

id
==
1
2
3

Is it possible to add in my query a clause like :

WHEN not matched AND source.operation = "DELETE" THEN ERROR("...")

This does not work (ERROR is unexpected) :

Syntax error: Expected keyword DELETE or keyword INSERT or keyword UPDATE but got identifier "ERROR"

If this is not possible with MERGE query, is there a way to rewrite it to a similar query to update atomically my target table as I expect?

norbjd
  • 10,166
  • 4
  • 45
  • 80

1 Answers1

1

You could generate an error yourself. Something like:

WHEN not matched AND source.operation = 'DELETE' THEN 
    INSERT (id)
       VALUES ( CAST(source.operation as int64) )

I haven't intentionally tried to generate errors in BigQuery, but I don't think there is a function that automatically does so.

As proposed by @norbjd:

WHEN not matched AND source.operation = 'DELETE' THEN 
    INSERT (id)
       VALUES ( ERROR('ERROR:  DELETE operation encountered') )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your answer. The destination type in `CAST` cannot be `int` (not a valid BigQuery type) but `numeric`, `int64`, or something else valid; however, the solution still applies. – norbjd Mar 05 '19 at 14:00
  • 1
    In BigQuery, the `ERROR` function exists to generate an error (https://cloud.google.com/bigquery/docs/reference/standard-sql/debugging_functions#error), but is not usable as-is, as only `DELETE` or `INSERT` is expected after `WHEN ... THEN`. But based on your solution, I could write : `INSERT (id) VALUES (ERROR('Error message'))` to have a clearer message, even if it seems like a hack. – norbjd Mar 05 '19 at 14:06