Fork me on GitHub
#sql
<
2020-04-15
>
markmarkmark15:04:18

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.

markmarkmark15:04:00

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?

jtth19:04:16

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)
gives
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]
It works fine without the SELECT email, :email and conditional bits, but doesn’t validate. Or should I not worry about this and do this in validation with struct or something and a separate DB call to check if the email address exists?

curtis.summers20:04:11

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

jtth20:04:26

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

codonnell22:04:27

@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.