Postgresql UPDATE LEFT JOIN

Question

So, I have the following code that updates a language string table, based on an id, and a language code. If I perform a SELECT using the same from expression, it selects just one row. If I do update, it updates all rows. Where am I going wrong with this?

UPDATE shopmaster.catalog_lang SET shortname='TEST' 
FROM shopmaster.catalog_lang cl LEFT JOIN shopmaster.lang l ON cl.langid=l.langid 
WHERE cl.catalogid=7 AND l.code='fr';

Here's the definition of the two tables:

CREATE TABLE IF NOT EXISTS shopmaster.lang(
    langid SERIAL,
    name TEXT,
    code TEXT,
    active BOOLEAN,
    donotdelete BOOLEAN,
    PRIMARY KEY (langid)


CREATE TABLE IF NOT EXISTS shopmaster.catalog_lang(
    catalogid INT references shopmaster.catalog(catalogid),
    langid INT references shopmaster.lang(langid),
    title TEXT,
    shortname TEXT,
    dirname TEXT,
    PRIMARY KEY (catalogid, langid)
);

Quote from the manual "Note that the target table must not appear in the from_list, unless you intend a self-join". The SELECT equivalent of what you are doing is select * from catalog_lang cross join catalog_lang left join lang on ... — user330315
– user330315, Commented Aug 24, 2018 at 10:49
BTW: why the LEFT join? (combined with WHERE ... AND l.code='fr'; — joop
– joop, Commented Aug 24, 2018 at 10:54

Gordon Linoff · Accepted Answer · 2018-08-24 10:49:52Z

3

Don't repeat the table being updated in the FROM. So:

UPDATE shopmaster.catalog_lang cl
    SET shortname = 'TEST' 
FROM shopmaster.lang l 
WHERE cl.langid = l.langid AND cl.catalogid = 7 AND l.code = 'fr';

In Postgres each reference to the table is separate. Your update is equivalent to this SELECT:

SELECT . . .
FROM shopmaster.catalog_lang CROSS JOIN
     shopmaster.catalog_lang cl LEFT JOIN
     shopmaster.lang l
     ON cl.langid = l.langid 
WHERE cl.catalogid = 7 AND l.code = 'fr';

And this is definitely not what you intend.

answered Aug 24, 2018 at 10:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Postgresql UPDATE LEFT JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related