Greg Stark wrote:
>
> Tom Lane <[EMAIL PROTECTED]> writes:
>
> > UPDATE totals SET
> > xmax = ss.xmax, xmin = ss.xmin, ...
> > FROM
> > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> > WHERE groupid = ss.groupid;
> >
> ...
> >
> > Of course this
Tom Lane <[EMAIL PROTECTED]> writes:
> UPDATE totals SET
> xmax = ss.xmax, xmin = ss.xmin, ...
> FROM
> (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> WHERE groupid = ss.groupid;
>
...
>
> Of course this syntax isn't standard either ... but we a
Hannu Krosing <[EMAIL PROTECTED]> writes:
> the more accurate (nonstandard) syntax could have been
>
> SELECT src.val,
>tgt.val
> FROM updatesrc as src FOR UPDATE,
>updatetgd as tgt
> WHERE src.id = tgt.id
>SET src.val = tgt.val
> ;
The syntax in Oracle, for example, wou
Tom Lane kirjutas K, 19.03.2003 kell 16:46:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I wasn't sure it made logical sense to allow correlated subqueries in
> > FROM because the FROM is processed before the WHERE.
>
> It doesn't; in fact it violates the whole semantic model of SQL,
> as far as
Bruce Momjian kirjutas E, 17.03.2003 kell 20:49:
> With no one replying on how to do correlated subqueries in FROM for
> UPDATE,
Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug,
so the way to do correlated subqueries in FROM for UPDATE would be to
fix this bug ;)
All commo
t; <[EMAIL PROTECTED]>
Sent: Wednesday, February 19, 2003 10:18 PM
Subject: Re: [HACKERS] request for sql3 compliance for the update command
> Justin,
>
> This is certainly the case here. I think IBM is deprecating informix,
> and many informix users are being forced to make a ch
While I can see a subquery in UPDATE as working in most cases:
UPDATE tab
SET col - t.col
FROM (SELECT col from xx) AS t
WHERE ...
but I don't see that working for correlated subqueries, where you want
to set a column based on a value you are updating. (Many use
Given that the direction of the spec seems to be headed towards the
desired syntax, can we put this on the TODO list?
Dave
On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
> Scott,
>
> I can't find page 858 in that document, is it the right one?
>
> also the link s/b ?
>
> ftp://ftp.sqlstandar
On Thu, 20 Feb 2003, Kevin Brown wrote:
> Tom Lane wrote:
> > UPDATE totals SET
> > xmax = ss.xmax, xmin = ss.xmin, ...
> > FROM
> > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> > WHERE groupid = ss.groupid;
>
> As long as any individual item th
> -Original Message-
> From: Mike Aubury [mailto:[EMAIL PROTECTED]]
> Sent: 20 February 2003 19:10
> To: Dave Page; Tom Lane; Hannu Krosing
> Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers
> Subject: Re: [HACKERS] request for sql3 compliance for the
> update com
Tom Lane <[EMAIL PROTECTED]> writes:
> UPDATE totals SET
> xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid),
> ...
>
> but that is awfully tedious and will be inefficiently implemented. This
> is what Bruce is worried about. On the other hand, one could
Tom Lane wrote:
> UPDATE totals SET
> xmax = ss.xmax, xmin = ss.xmin, ...
> FROM
> (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> WHERE groupid = ss.groupid;
As long as any individual item that you can express in the
parenthesized (Informix) syntax
The right URL (I'll get it eventually) is
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf
That time I exactly copied the URL. sorry for the wrong one previously.
On 20 Feb 2003, Dave Cramer wrote:
> Scott,
>
> Thanks for the reference, I think the act
On Thu, Feb 20, 2003 at 09:31:21AM -0500, Tom Lane wrote:
> about. I originally understood Dave to be asking for parens to be
> allowed around individual target column names, which seems a useless
> frammish to me. What Bruce has pointed out is that a syntax that lets
> you assign multiple column
sorry, it's the -02 document.
just change the last 01 to 02 and you'll get the right one.
On 20 Feb 2003, Dave Cramer wrote:
> Scott,
>
> I can't find page 858 in that document, is it the right one?
>
> also the link s/b ?
>
> ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4
Scott,
Thanks for the reference, I think the actual document is
ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
and it is in section 14.12
on or about page 839
Dave
On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> On Thu, 20 Feb 2003, Tom Lane w
Informix supports 2 different styles for the update - your one would have to
be written :
UPDATE djp SET(col1, col2) = ((SELECT col1,col2 FROM some_other_table))
Notice the double brackets !
The first signifies a list of values - the second is the brackets around the
subquery...
(NB If you tr
Scott,
I can't find page 858 in that document, is it the right one?
also the link s/b ?
ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
Dave
On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> On Thu, 20 Feb 2003, Tom Lane wrote:
>
> > Hannu Krosin
On Thu, 20 Feb 2003, Tom Lane wrote:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > Are you against it just on grounds of cleanliness and ANSI compliance,
> > or do you see more serious problems in letting it in ?
>
> At this point it seems there are two different things being tossed
> about. I
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
> Sent: 20 February 2003 14:31
> To: Hannu Krosing
> Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers
> Subject: Re: [HACKERS] request for sql3 compliance for the
> update command
>
>
> H
Hannu Krosing <[EMAIL PROTECTED]> writes:
> Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
>> However, what solution do we have for UPDATE (coll...) = (select val...)
>> for folks? It is awkward to repeat a query multiple times in an UPDATE.
> hannu=# update target set
> hannu-# a = source.a1,
Hannu Krosing <[EMAIL PROTECTED]> writes:
> Are you against it just on grounds of cleanliness and ANSI compliance,
> or do you see more serious problems in letting it in ?
At this point it seems there are two different things being tossed
about. I originally understood Dave to be asking for paren
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
> Agreed folks are going to have bigger problems from Informix than just
> this, and in fact I used Informix for years and didn't know they allowed
> this.
>
> However, what solution do we have for UPDATE (coll...) = (select val...)
> for folks? It
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
> Agreed folks are going to have bigger problems from Informix than just
> this, and in fact I used Informix for years and didn't know they allowed
> this.
>
> However, what solution do we have for UPDATE (coll...) = (select val...)
> for folks? It
Tom Lane kirjutas K, 19.02.2003 kell 21:12:
> Dave Cramer <[EMAIL PROTECTED]> writes:
> > Ok, if a patch were submitted to the parser to allow the syntax in
> > question would it be considered?
>
> I would vote against it ... but that's only one vote.
Are you against it just on grounds of cleanli
On Wed, Feb 19, 2003 at 04:37:33PM +0100, Peter Eisentraut wrote:
> That's not what my copy says.
Strange. I just looked at all the docs I have and all have it listed the
way Dave wrote. So I seem to have to update my docs. Peter, could you
send me a copy?
Michael
--
Michael Meskes
Email: [EMAIL
On Wed, Feb 19, 2003 at 12:29:12PM -0500, Tom Lane wrote:
> SQL99. Looks like the parens got lost again by the time of the final
> spec.
I don't think the parens really matter. It's just the different ordering
of columns and values.
Michael
--
Michael Meskes
Email: [EMAIL PROTECTED]
ICQ: 179140
Christopher Kings-Lynne wrote:
>
> > BTW, looking at the SQL99 standard, I see that you can do
> >
> > UPDATE table SET ROW = foo WHERE ...
> >
> > where foo is supposed to yield a row of the same rowtype as table
> > --- I didn't dig through the spec in detail, but I imagine foo can
> > be a su
> BTW, looking at the SQL99 standard, I see that you can do
>
> UPDATE table SET ROW = foo WHERE ...
>
> where foo is supposed to yield a row of the same rowtype as table
> --- I didn't dig through the spec in detail, but I imagine foo can
> be a sub-select. I don't care a whole lot for that, t
On 19 Feb 2003, Dave Cramer wrote:
> Yes, the company in question is more than evaluating it; this request is
> a result of a project to port their application to postgres.
Ahh. I thought you were referring to IBM. That is, that IBM was evaluating
Postgres...
Gavin
---(
Bruce Momjian <[EMAIL PROTECTED]> writes:
> While I don't see the syntax of:
> update table set (col...) = ( val...)
> as valuable compared to separate col=val assignments, I do see a value
> in allowing subqueries in such assignments:
> update table set (col...) = ( select val ..)
Hm.
Agreed folks are going to have bigger problems from Informix than just
this, and in fact I used Informix for years and didn't know they allowed
this.
However, what solution do we have for UPDATE (coll...) = (select val...)
for folks? It is awkward to repeat a query multiple times in an UPDATE.
Justin Clift <[EMAIL PROTECTED]> writes:
> As a thought, will it add significant maintenance penalties or be
> detrimental?
Well, yes it will if you look at the big picture. In the past we've
generally regretted it when we put in nonstandard features just to be
compatible with some other databas
On 19 Feb 2003, Dave Cramer wrote:
> Justin,
>
> This is certainly the case here. I think IBM is deprecating informix,
> and many informix users are being forced to make a change, and they are
> seriously considering postgres as an alternative.
Do you have any evidence that they are evaluating i
After a long battle with technology,[EMAIL PROTECTED] (Mike Aubury), an earthling, wrote:
> On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote:
>> I have a customer with a rather large application which uses this
>> syntax, because they were using informix. There is also a rather
>> interesti
Justin,
This is certainly the case here. I think IBM is deprecating informix,
and many informix users are being forced to make a change, and they are
seriously considering postgres as an alternative.
It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before
making this decision as w
Tom Lane wrote:
Dave Cramer <[EMAIL PROTECTED]> writes:
Ok, if a patch were submitted to the parser to allow the syntax in
question would it be considered?
I would vote against it ... but that's only one vote.
As a thought, will it add significant maintenance penalties or be
detrimental?
There
> While I don't see the syntax of:
>
> update table set (col...) = ( val...)
>
> as valuable compared to separate col=val assignments, I do see a value
> in allowing subqueries in such assignments:
>
> update table set (col...) = ( select val ..)
>
> Without it, you have to do separate subquery
While I don't see the syntax of:
update table set (col...) = ( val...)
as valuable compared to separate col=val assignments, I do see a value
in allowing subqueries in such assignments:
update table set (col...) = ( select val ..)
Without it, you have to do separate subquery st
On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote:
> I have a customer with a rather large application which uses this
> syntax, because they were using informix. There is also a rather
> interesting 4GL project called aubit which is on sourceforge. They would
> also like to see this support
Patrick,
No, they support the syntax:
update table set (col1, col2, col3) = ( val1, val2, val3 )
I have a customer with a rather large application which uses this
syntax, because they were using informix. There is also a rather
interesting 4GL project called aubit which is on sourceforge. They w
On Wed, Feb 19, 2003 at 07:31:35AM -0500, Dave Cramer wrote:
> Bruce,
>
> Can you chime in with your support here?
>
> Dave
>
> I have a large customer who is converting from informix to postgres and
> they have made extensive use of
>
> update table set (col...) = ( val...)
>
> as a first pa
Dave Cramer <[EMAIL PROTECTED]> writes:
> Ok, if a patch were submitted to the parser to allow the syntax in
> question would it be considered?
I would vote against it ... but that's only one vote.
regards, tom lane
---(end of broadcast)---
Ok, if a patch were submitted to the parser to allow the syntax in
question would it be considered?
Dave
On Wed, 2003-02-19 at 12:29, Tom Lane wrote:
> Dave Cramer <[EMAIL PROTECTED]> writes:
> > Referring to
> > http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt
> > the follow
Dave Cramer <[EMAIL PROTECTED]> writes:
> Referring to
> http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt
> the following grammar exists
> is the reference above valid?
Sep 93? That would be an extremely early draft of what eventually became
SQL99. Looks like the parens got
Peter,
Referring to
http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt
the following grammar exists
is the reference above valid?
as for tom's reply there are left paren, and right paren.
Dave
On Wed, 2003-02-19 at 10:37, Peter Eisentraut wrote:
> Dave Cramer writes:
>
>
Dave Cramer <[EMAIL PROTECTED]> writes:
> It would appear that this is SQL3 compliant
> ::=
>
> ::=
>
> |
I see no parentheses allowed there in the SQL99 spec. Encourage your
customer to use standard syntax.
regards, tom lane
---(en
Dave Cramer writes:
> update table set (col...) = ( val...)
> It would appear that this is SQL3 compliant
>
> ::=
>
>
> ::=
>
> |
That's not what my copy says.
::=
[ { }... ]
::=
|
::=
Bruce,
Can you chime in with your support here?
Dave
I have a large customer who is converting from informix to postgres and
they have made extensive use of
update table set (col...) = ( val...)
as a first pass would it be possible to translate this in the parser to
update table set col=val
I have a large customer who is converting from informix to postgres and
they have made extensive use of
update table set (col...) = ( val...)
as a first pass would it be possible to translate this in the parser to
update table set col=val
It would appear that this is SQL3 compliant
::=
50 matches
Mail list logo
