Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Why is test_one in the from clause? update joins whatever is in
> the from clause to the table being updated. You almost never need
> it repeated in the from clause.
This is actually one of the nastier "gotchas" in converting from
Sybase ASE or MS SQL Server to PostgreSQL -- there are syntactically
identical UPDATE statements with very different semantics when a
FROM clause is used in an UPDATE statement. You need to do what the
OP was showing to use an alias with the target table under those
other products.
I suppose it might be possible to generate a warning when it appears
that someone is making this mistake, but it wouldn't be easy and
would probably not be worth the carrying cost. The test would need
to be something like:
(1) The relation which is the target of the UPDATE has no alias.
(2) There is a FROM clause which included the target relation (with
an alias).
(3) There aren't any joining references between the UPDATE target
and the relation(s) in the FROM clause.
-Kevin