Re: BUG #5850: UPDATE statement fails when using aliases

On Wed, Jan 26, 2011 at 06:05:40PM +0000, Joshua Farray wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5850
> Logged by:          Joshua Farray
> Email address:      farray@gmail.com
> PostgreSQL version: 9.0.2
> Operating system:   Win7 x64
> Description:        UPDATE statement fails when using aliases
> Details:
>
> Make a table:
> > CREATE TABLE foos
> > (
> >   foo_id serial,
> >   foo_text varchar(255)
> > );
>
> Update the table using an alias:
> > UPDATE foos AS t
> > SET t.foo_text = '';
>
> Expected output:
> > UPDATE 0
>
> Actual output:
> > ERROR:  column "t" of relation "foos" does not exist
> > LINE 2: SET t.foo_text = '';
> >             ^

you don't need to and can't use alias on the left side in assignment.

proper query:
update foos as t
set foo_text = '';

this works even if you join another table that has foo_text column, as
update knows which table you're updating.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007