Re: Urgent help needed- alias name in update statement

Mike/venkat
Sorry, you're right.
update blanktable b
set recno = b.recno
where b.recno = b.recno;

seems to work ok, but if I change that as below, the error occurs:
update blanktable b
set b.recno = b.recno
where b.recno = b.recno;
ERROR: column "b" of relation "blanktable" does not exist
LINE 2: set b.recno = b.recno
^

So that would imply that simply setting 'name' in Venkat's example
(rather than x.name) should work ok.

On Tue, 2010-03-09 at 17:42 +0200, Michael Wood wrote:

> On 9 March 2010 16:03, Steve T <steve(at)retsol(dot)co(dot)uk> wrote:
> >
> > Venkat
> > Shouldn't the expression have an 'as'?
> >
> > IE
> > UPDATE mytable AS x
> > SET x.name = 'asdf'
> > WHERE x.no = 1
>
> AS doesn't make a difference. It's optional.
>
> I don't know why the above doesn't work. I've encountered something
> in the past which may be related:
>
> SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
> FROM mytable
> WHERE something IS NOT NULL
> AND LOWER(SPLIT_PART(something, '^', 3)) <> ''
> AND other = 123;
>
> This works, but what I want to do is the following:
>
> SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
> FROM mytable
> WHERE something IS NOT NULL
> AND blah <> ''
> AND other = 123;
>
> This does not work and I don't know why not.
>
> The error message is:
>
> ERROR: column "blah" does not exist
> LINE 4: AND blah <> ''
> ^
>
> which is not exactly the same as the error Venkat is getting, but it's
> pretty close.
>
> P.S. Venkat, it is called "PostgreSQL" or "Postgres". Not "Postgre".
>
> > On Tue, 2010-03-09 at 19:21 +0530, venkatrao(dot)b(at)tcs(dot)com wrote:
> >
> > Hello,
> >
> > In postgre, when i am trying to give alias name in update statement like below -
> > ---------------------------------
> > update mytable x
> > set x.name = 'asdf'
> > where x.no = 1
> > -------------------------------
> >
> > is giving error - mytable is not having col x.
> >
> > We have migrated code from oracle to postgre 8.4. Is there any solution for this.
> > (functions were compiled without any compilation errors - now when we are trying to run these we are getting problems)
>
> --
> Michael Wood <esiotrot(at)gmail(dot)com>
>

Steve Tucknott
ReTSol Ltd

DDI: 01323 488548