Clojurians Log v2

say that I have a set up like: table main, table junctionA which references main, and table junctionB which references main. I want to delete a row from main, but only if junctionA doesn't have any rows that currently reference the row in main. It doesn't matter if junctionB has any rows referencing main.

is there a downside to just deleting the rows from junctionA and rolling that back if it deletes anything... as opposed to querying junctionA first and just not doing the deletes if it comes back with count > 0?

I’m trying to make a function in hugsql that inserts a row only when no other row in the table has a value (no duplicate email addresses, primary key is an auto-incremented ID, database is H2). What’s happening here:

-- :name create-user-2! :insert :raw
-- :doc creates a new user record so long as the email address does not already exist
INSERT INTO users
(first_name, last_name, email, pass)
VALUES (:first_name, :last_name, LOWER(:email), :pass)
SELECT email, :email
WHERE NOT EXISTS (SELECT * FROM users WHERE email = :email)

Execution error (JdbcSQLSyntaxErrorException) at org.h2.message.DbException/getJdbcSQLException (DbException.java:453).
Syntax error in SQL statement "INSERT INTO USERS
(FIRST_NAME, LAST_NAME, EMAIL, PASS)
VALUES (?, ?, LOWER(?), ?)
SELECT[*] EMAIL, ?
WHERE NOT EXISTS (SELECT * FROM USERS WHERE EMAIL = ?)"; SQL statement:
INSERT INTO users
(first_name, last_name, email, pass)
VALUES (?, ?, LOWER(?), ?)
SELECT email, ?
WHERE NOT EXISTS (SELECT * FROM users WHERE email = ?) [42000-200]

@jtth Isn’t your first SELECT missing a FROM clause?

i have no idea, i’m copying stuff off posts and books, i barely know anything about sql

@jtth I've never used h2, and from some cursory reading it sounds like it has a number of compatibility modes it can run in which affect its behavior. The feature it sounds like you're looking for is often referred to as "upsert"; you may have some luck googling that along with the settings you're using to run h2.