Re: Urgent help needed- alias name in update statement
От | Steve T |
---|---|
Тема | Re: Urgent help needed- alias name in update statement |
Дата | |
Msg-id | 1268150154.2186.129.camel@retsol6400 обсуждение исходный текст |
Ответ на | Re: Urgent help needed- alias name in update statement (Michael Wood <esiotrot@gmail.com>) |
Список | pgsql-novice |
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:
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@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>
|
В списке pgsql-novice по дате отправления: