Thread: Why can't you define a table alias on an update?
This is the query I posted a performance question on, but I have a SQL standard question about it too: update missing_ids set mtransts = a.mtransts, mtranmemtp = a.mtranmemtp from memtran as a where a.mtranmemid = missing_ids.mtranmemid and a.mtranseq = missing_ids.mtranseq Why can't you define an alias on the primary table in an update query? That would make the where clause in the above a bit easier to write and read, since both the primary table and the table it is being updated from have the same column names. -- Mike Nolan
nolan@celery.tssi.com writes:
> This is the query I posted a performance question on, but I have a
> SQL standard question about it too:
> Why can't you define an alias on the primary table in an update query?
Because there's no such syntax in the SQL standard.
It seems like a reasonable extension, but looking at the grammar just
now, I think that we'd have to turn SET from an unreserved keyword to a
reserved word to make this work. Not sure how many peoples' databases
that would break ... but we'd probably get a few complaints ...
regards, tom lane
On Sun, Jun 15, 2003 at 06:36:57PM -0400, Tom Lane wrote: > nolan@celery.tssi.com writes: > > This is the query I posted a performance question on, but I have a > > SQL standard question about it too: > > Why can't you define an alias on the primary table in an update query? > > Because there's no such syntax in the SQL standard. > > It seems like a reasonable extension, but looking at the grammar just > now, I think that we'd have to turn SET from an unreserved keyword to a > reserved word to make this work. Not sure how many peoples' databases > that would break ... but we'd probably get a few complaints ... Would it be reasonable to have a setting that enabled/disabled this? Because I would **LOVE** to have aliases for UPDATE! -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <jim@nasby.net> writes:
> On Sun, Jun 15, 2003 at 06:36:57PM -0400, Tom Lane wrote:
>> It seems like a reasonable extension, but looking at the grammar just
>> now, I think that we'd have to turn SET from an unreserved keyword to a
>> reserved word to make this work. Not sure how many peoples' databases
>> that would break ... but we'd probably get a few complaints ...
> Would it be reasonable to have a setting that enabled/disabled this?
No, unless you want to have two complete bison parsers in there. AFAIK
there's no good way to alter the reserved-word status of a keyword on
the fly. So either we do it, or not.
I'm not necessarily opposed to doing it, I just wanted to raise a flag
and see if anyone reading this thread would complain.
regards, tom lane
Couldn't resist to not comment on this one: I find this feature would be extremely useful considering the fact that postgres supports the FROM clause in updates. This is already a useful extension to the SQL standard, so why not make it better ? I vote +1 to implement this ! Cheers, Csaba. On Mon, 2003-06-16 at 15:42, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > On Sun, Jun 15, 2003 at 06:36:57PM -0400, Tom Lane wrote: > >> It seems like a reasonable extension, but looking at the grammar just > >> now, I think that we'd have to turn SET from an unreserved keyword to a > >> reserved word to make this work. Not sure how many peoples' databases > >> that would break ... but we'd probably get a few complaints ... > > > Would it be reasonable to have a setting that enabled/disabled this? > > No, unless you want to have two complete bison parsers in there. AFAIK > there's no good way to alter the reserved-word status of a keyword on > the fly. So either we do it, or not. > > I'm not necessarily opposed to doing it, I just wanted to raise a flag > and see if anyone reading this thread would complain. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
> I'm not necessarily opposed to doing it, I just wanted to raise a flag > and see if anyone reading this thread would complain. Oracle permits table aliases on an update statement, though in Oracle the word 'as' to denote a table alias is not permitted while 'as' is optional for column aliases, whereas in pgsql it is REQUIRED in both cases. Tom, is there an online reference to the full SQL standard for those of us who don't have copies of it on our bookshelf or engrained in memory? :-) -- Mike Nolan
nolan@celery.tssi.com writes:
> Tom, is there an online reference to the full SQL standard for those of us
> who don't have copies of it on our bookshelf or engrained in memory? :-)
See doc/FAQ_DEV item 1.12. I tend to use the draft specs, just because
they're plain text and so easy to search ...
regards, tom lane
I also add my vote for allowing aliases in updates. BTW, in my code I never use 'as' for table aliases. For column aliases however, postgresql requires it. Avi On Monday, Jun 16, 2003, at 09:06 America/Chicago, nolan@celery.tssi.com wrote: >> I'm not necessarily opposed to doing it, I just wanted to raise a flag >> and see if anyone reading this thread would complain. > > Oracle permits table aliases on an update statement, though in Oracle > the word 'as' to denote a table alias is not permitted while 'as' is > optional for column aliases, whereas in pgsql it is REQUIRED in both > cases. > > Tom, is there an online reference to the full SQL standard for those > of us > who don't have copies of it on our bookshelf or engrained in memory? > :-)
On Mon, Jun 16, 2003 at 09:42:21AM -0400, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > Would it be reasonable to have a setting that enabled/disabled this? > > No, unless you want to have two complete bison parsers in there. AFAIK > there's no good way to alter the reserved-word status of a keyword on > the fly. So either we do it, or not. Is a startup option considered 'on-the-fly' too? Backwards compatibility is always so much fun... -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
TODO updated:
* Allow UPDATE, DELETE to handle table aliases for self-joins [delete]
---------------------------------------------------------------------------
Csaba Nagy wrote:
> Couldn't resist to not comment on this one:
>
> I find this feature would be extremely useful considering the fact that
> postgres supports the FROM clause in updates. This is already a useful
> extension to the SQL standard, so why not make it better ?
> I vote +1 to implement this !
>
> Cheers,
> Csaba.
>
> On Mon, 2003-06-16 at 15:42, Tom Lane wrote:
> > "Jim C. Nasby" <jim@nasby.net> writes:
> > > On Sun, Jun 15, 2003 at 06:36:57PM -0400, Tom Lane wrote:
> > >> It seems like a reasonable extension, but looking at the grammar just
> > >> now, I think that we'd have to turn SET from an unreserved keyword to a
> > >> reserved word to make this work. Not sure how many peoples' databases
> > >> that would break ... but we'd probably get a few complaints ...
> >
> > > Would it be reasonable to have a setting that enabled/disabled this?
> >
> > No, unless you want to have two complete bison parsers in there. AFAIK
> > there's no good way to alter the reserved-word status of a keyword on
> > the fly. So either we do it, or not.
> >
> > I'm not necessarily opposed to doing it, I just wanted to raise a flag
> > and see if anyone reading this thread would complain.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
> * Allow UPDATE, DELETE to handle table aliases for self-joins [delete] Thanks. I need to set up a system for 7.4, in the hopes that eventually I can become a contributor rather than just a consumer. I've got another SQL-related question about UPDATE. I need to update several columns in table1 with either the minimum or maximum value of a corresponding column in a matching set of rows in table2. I'd like to be able to do it in a single query. The following doesn't work, it updates just one row. UPDATE table1 set t1_col1 = max(t2_col1), t1_col2 = min(t2_col2) from table2 where t1_key = t2_key I don't seem to be able to use a group clause. Is there a way to write this query? -- Mike Nolan
On Mon, Jun 23, 2003 at 21:31:01 -0500,
nolan@celery.tssi.com wrote:
>
> I need to update several columns in table1 with either the minimum or
> maximum value of a corresponding column in a matching set of rows in
> table2. I'd like to be able to do it in a single query.
>
> The following doesn't work, it updates just one row.
>
> UPDATE table1
> set t1_col1 = max(t2_col1),
> t1_col2 = min(t2_col2)
> from table2
> where t1_key = t2_key
>
> I don't seem to be able to use a group clause.
>
> Is there a way to write this query?
I think something like the following is best. I rewrote the max and min
functions in a way that will work well if there is a combined index on
t2_key and t2_col1 as well as t2_key and t2_col2. Another approach would
be to join to a group by select from table2. There may be some circumstances
where that gives you better performance, but I can't think of a particular
circumstance where that is likely offhand.
UPDATE table1
set t1_col1 =
(select t2_col1 from table2 where t1_key = t2_key
order by t2_key desc, t2_col1 desc limit 1),
set t1_col2 =
(select t2_col2 from table2 where t1_key = t2_key
order by t2_key, t2_col2 limit 1);
> I think something like the following is best. I rewrote the max and min > functions in a way that will work well if there is a combined index on > t2_key and t2_col1 as well as t2_key and t2_col2. Another approach would > be to join to a group by select from table2. There may be some circumstances > where that gives you better performance, but I can't think of a particular > circumstance where that is likely offhand. I hadn't thought of using LIMIT in that context, a nice trick to remember! That worked fairly well even with an index just on the keys. It took about the same time as just loading the data from the first record in each key group. -- Mike Nolan