PostgreSQL中的update语句对alias的支持没有select中支持的完善,有个不大不小的问题。虽然不严重,但不知道的话还是会引起很大的困惑。
首先看一下PostgreSQL对update的帮助:
- Command: UPDATE
- Description: update rows of a table
- Syntax:
- UPDATE [ ONLY ] table [ [ AS ] alias ]
- SET { column = { expression | DEFAULT } |
- ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
- [ FROM fromlist ]
- [ WHERE condition | WHERE CURRENT OF cursor_name ]
- [ RETURNING * | output_expression [ AS output_name ] [, ...] ]
- update xuepeng.bb as b set b.a = 3 where a = 2;
- ERROR: column "b" of relation "bb" does not exist
- LINE 1: update xuepeng.bb as b set b.a = 3 where a = 2;
因此我们虽然使用了alias,但是在set的语句中还是不能使用这个别名的。有意思的是,在where的子语句中,却是能够正常使用alias。以下的语句是正确的:
- update xuepeng.bb as b set a = 3 where b.a = 2;