CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
dks551
dks551

Reputation: 1113

How to insert into a table based on a condition like - if there is no data in the same table

Below are my table structures. I want to keep only one row inside TABLE2 for maximum date and hour.

TABLE1 :
--------------------------------------
 col1   | col2  | col3 | date | hour |
--------------------------------------


TABLE2 :
-----------------
 date   | hour  |
-----------------

Condition : Insert into TABLE2 from TABLE1 only when there is no data in TABLE2.

Description : Extract the max(date) and for that max date extract the max(hour) from TABLE1. And the only condition here is insert the above data into TABLE2 only when there is no row in TABLE2.

How can I achieve this in Snowflake SQL ?

Upvotes: 1

Views: 1399

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

I would do this as:

insert into table2 (date, hour)
    select mydate, hour
    from table1 
    where not exists (select 1 from table2)
    order by mydate desc, hour desc 
    limit 1;

Upvotes: 2

Stuart Ozer
Stuart Ozer

Reputation: 1384

You can use a strategy like this:

insert into table2
   with rowcount as (select count(1) cnt from table2)
select mydate, hour from table1 
   join rowcount
   where rowcount.cnt = 0
   order by mydate desc, hour desc 
   limit 1;

Upvotes: 0

Related Questions