Drizzle Team•9mo ago

Update a field only if currently null

I have a users table with a field called mainListId which is null by default. whenever a user creates a new list, I need to check if *mainListId *is null, and if so, I update this field to contain the new list id. Is it somehow posibble to update *mainListId * only if it's currently null, so that I won't have to make another query just to check if it's currently null or not?

Solution:

In that case it's pretty easy:

db.update(users).set({ mainListId: providedId }).where(and(eq(users.id, theIdOfTheUserToUpdate), isNull(users.mainListId)))

db.update(users).set({ mainListId: providedId }).where(and(eq(users.id, theIdOfTheUserToUpdate), isNull(users.mainListId)))

...

Jump to solution

6 Replies

Angelelz•9mo ago

What type of column is mainListId? is it an array in PG?

A50•9mo ago

the mainListId field is simply an integer which is a foreign key to the lists table

Angelelz•9mo ago

So how do you handle it if mainListId is not null?

A50•9mo ago

In that case I don't need to update *mainListId * (because this is not the first list the user has created)

Solution

Angelelz•9mo ago

In that case it's pretty easy:

db.update(users).set({ mainListId: providedId }).where(and(eq(users.id, theIdOfTheUserToUpdate), isNull(users.mainListId)))

db.update(users).set({ mainListId: providedId }).where(and(eq(users.id, theIdOfTheUserToUpdate), isNull(users.mainListId)))

A50•9mo ago

that's cool, thanks a lot!

Gaming

Programming

Update a field only if currently null