CODEWITHSUNDEEP
sqlgoogle-bigquerysql-updatesql-merge
norbjd
norbjd

Reputation: 11257

BigQuery : how to make a MERGE operation fail on some conditions?

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?

Upvotes: 0

Views: 1271

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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') )

Upvotes: 1

Related Questions