On 9 March 2010 16:03, Steve T <steve@retsol.co.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.b@tcs.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@gmail.com>
