3

I want to update the column leaf_category with TRUE where the category is not a parent category. It works as a select statement:

 select 
     c1.id, c1.name, c1.slug, c1.level, c2.parent_id, c2.name, c2.slug, c2.level 
 from
     catalog_category c1 
 left outer join 
     catalog_category c2 on 
     (c1.id = c2.parent_id)
 where 
     c2.parent_id is null;

However, the corresponding UPDATE sets all the columns to TRUE.

update catalog_category 
set leaf_category = True
from
    catalog_category c1 
left outer join 
    catalog_category c2 on 
    (c1.id = c2.parent_id)
 where 
     c2.parent_id is null;

Is an UPDATE like that possible at all?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
slooow
  • 329
  • 1
  • 3
  • 10

1 Answers1

11

You are just missing a connecting WHERE clause:

UPDATE catalog_category c
SET    leaf_category = true
FROM   catalog_category c1 
LEFT   JOIN catalog_category c2 ON c1.id = c2.parent_id
WHERE  c.id = c1.id
AND    c2.parent_id IS NULL;

This form with NOT EXISTS is probably faster, doing the same:

UPDATE catalog_category c
SET    leaf_category = true
WHERE  NOT EXISTS (
    SELECT FROM catalog_category c1
    WHERE  c1.parent_id = c.id
    );

The manual for UPDATE.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    @Dems: You can use an alias for the table to be updated, but the `c1` relation is in the `FROM` clause. In PostgreSQL, unlike MySQL, only *one* table can be updated at a time. This corresponds to the SQL standard if I am not mistaken - while the additional `FROM` clause is a PostgreSQL extension of the standard. – Erwin Brandstetter Jan 07 '12 at 03:54
  • 1
    Thanks Erwin. It worked and it is shorter. By experimenting I found by swapping parent_id and id in the subclause it returns the root categories. – slooow Jan 09 '12 at 02:16
  • 1
    The first statement will not work as PostgreSQL does not support the `JOIN` keyword in FROM clause for the `UPDATE` statement. You have to use "implicit joins" in the WHERE clause –  May 04 '12 at 14:51
  • 1
    @a_horse_with_no_name: You are mistaken, the statement is tested and works. We've had the same thing a couple of days ago [here](http://stackoverflow.com/questions/10377302/postgresql-database-inner-join-query-error/10377333#comment13385825_10377333). – Erwin Brandstetter May 04 '12 at 15:08
  • 1
    Interesting. It is certainly not clearly visible in the manual. But as it links to the standard FROM clause I guess that's where it comes from. You live and learn ;) –  May 04 '12 at 15:13
  • 1
    @a_horse_with_no_name: I see now that I forgot to `@`-notify you in the other comment. Sorry about that. – Erwin Brandstetter May 04 '12 at 15:16