Skip site navigation (1) Skip section navigation (2)

Re: Urgent help needed- alias name in update statement

From: Michael Wood <esiotrot(at)gmail(dot)com>
To: steve(at)retsol(dot)co(dot)uk
Cc: venkatrao(dot)b(at)tcs(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: Urgent help needed- alias name in update statement
Date: 2010-03-09 15:42:51
Message-ID: 5a8aa6681003090742v73215253w7826eb62ca7089c5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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>

In response to

Responses

pgsql-novice by date

Next:From: Steve TDate: 2010-03-09 15:55:54
Subject: Re: Urgent help needed- alias name in update statement
Previous:From: Greg StarkDate: 2010-03-09 14:32:40
Subject: Re: has_schema_privilege function

pgsql-general by date

Next:From: Scot KreienkampDate: 2010-03-09 15:46:41
Subject: Re: autovacuum question
Previous:From: Scot KreienkampDate: 2010-03-09 15:25:52
Subject: Re: autovacuum question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group