Using LEFT JOIN in an embedded query in PostgreSQL

Question

Here is the query I'm having a problem with:

UPDATE entry SET is_locked_by = NULL
WHERE id IN (
              SELECT cee."entryId" FROM category_entries_entry AS cee
              WHERE cee."categoryId" IN (${descendantsWithParent.map(c => c.id).join()})
              LEFT JOIN category cat ON cee."categoryId" = cat.id WHERE NOT cat.is_locked
            );

Category relates to Entry as ManyToMany. And I want to update all entries where category.is_locked is false.

This query does not seem to work, it seems to ignore the WHERE NOT cat.is_locked.

What is wrong with this query?

join can not be used after where clause. it seems you are mixing function operator of JS in postgres query — Akhilesh Mishra
– Akhilesh Mishra, Commented Aug 17, 2020 at 16:48

Akhilesh Mishra · Accepted Answer · 2020-08-17 17:00:19Z

1

There is no problem with WHERE NOT cat.is_locked as long as is_locked is boolean. You should write your query like this.

UPDATE entry SET is_locked_by = NULL
WHERE id IN (
              SELECT cee."entryId" FROM category_entries_entry cee
              LEFT JOIN category cat ON cee."categoryId" = cat.id
              WHERE NOT cat.is_locked 
              and cee."categoryId" IN ( set of IDs)  -- you can use your statement in your program i. e. (${descendantsWithParent.map(c => c.id).join()}
            );

edited Aug 17, 2020 at 17:00

answered Aug 17, 2020 at 16:58

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Albert Over a year ago

Aah yeah seem so. Thank you, lemme check if it works.

Akhilesh Mishra Over a year ago

your value list should be proper after IN

Collectives™ on Stack Overflow

Using LEFT JOIN in an embedded query in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related