SQL - INSERT IF NOT EXISTS based on COUNT-CodePudding

I need to write an INSERT statement such that if a value in a column does not exist OR the COUNT of said value exists for less (<) than a certain number of rows the new data will be inserted (if the COUNT of the value is greater than or equal to said number nothing happens).

This SQL solves the first criteria, but not the second...

CREATE OR REPLACE TABLE day_count_table (day1 DATE, col1 STRING);
INSERT INTO day_count_table VALUES ('2022-04-01','Testing April 1, 2022');
INSERT INTO day_count_table VALUES ('2022-04-02','Testing April 2, 2022');
CREATE OR REPLACE TABLE day_count_table_temp (day1 DATE, col1 STRING);
INSERT INTO day_count_table_temp VALUES ('2022-04-03','Testing April 3, 2022');

-- This works for the first criteria, but not sure how to incorporate the COUNT criteria
INSERT INTO day_count_table
SELECT * FROM day_count_table_temp a
WHERE NOT EXISTS (SELECT 1 FROM day_count_table b WHERE b.day1 = a.day1);

CodePudding user response：

If you change your WHERE to:

WHERE 5 > (SELECT count(*) FROM day_count_table b WHERE b.day1 = a.day1);

Where 5 is the number you want to be less than - wouldn't that catch both circumstances... ie: If there's no rows, count(*) would be 0, if there are fewer than your limit, you're good to.