GBQ - Execute INSERT query ONLY if SELECT query returns results

This question has been asked before in many forms. But none of the solutions proposed worked for my case.

I am using GBQ.

I have this table:

        Hour            Orders
2022-01-12T00:00:00       12
2022-01-12T01:00:00       8
2022-01-12T02:00:00       9

I want to create a query to insert data into this table automatically per hour, under these conditions:

• If the "most recent hour" that I want to insert already exists, I do not want to insert it twice.

I tried the following SQL query :

IF EXISTS (SELECT 1 FROM `Table` WHERE Hour = var_most_recent_hour) 
    UPDATE `Table` SET Orders = var_most_recent_orders WHERE Hour = var_most_recent_hour
ELSE 
    INSERT INTO `Table` (Hour, Orders) VALUES (var_most_recent_hour, var_most_recent_orders)

This syntax is returning an error in GBQ, although the SQL syntax is usually accepted.

Is there a way to do this?

My priority is to insert without duplicates. I don't care about the UPDATE part in my query.

Ideally I want something like (I know this syntax does not exist):

IF NOT EXISTS (SELECT 1 FROM `Table` WHERE Hour = var_most_recent_hour) 
    INSERT INTO `Table` (Hour, Orders) VALUES (var_most_recent_hour, var_most_recent_orders)

Thank you

Solution 1:

Try Sample code below

declare  most_rcnt_hour time;

INSERT INTO dataset.targettable(...

SELECT *
FROM dataset.targettable T
JOIN (SELECT most_rcnt_hour AS most_rcnt_hour) as S
ON T.rcnt_hour <> S.most_rcnt_hour

Note that IF in BQ works differently. IF