pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE ... FROM vs standard SQL
6+ messages / 4 participants
[nested] [flat]
* UPDATE ... FROM vs standard SQL
@ 2002-06-07 10:33 Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 6+ messages in thread
From: Richard Huxton @ 2002-06-07 10:33 UTC (permalink / raw)
To: pgsql-sql
I've been happily using statements like
UPDATE a SET flag=1 FROM b WHERE a.id=b.id AND b.foo='x';
While PG's FROM extension makes life simple, I can't believe there's not a way
to do an update on a join using standard SQL. The two options I can think of
are:
1. using a sub-select
UPDATE a SET flag=1 WHERE a.id IN (SELECT id FROM b WHERE b.foo='X');
Which is fine, but no good for mysql, hits PG's speed issue with IN and a bit
clumsy for more complicated examples.
2. building an updatable view.
Am I missing something here?
TIA
- Richard Huxton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE ... FROM vs standard SQL
@ 2002-06-07 14:20 Jan Wieck <janwieck@yahoo.com>
parent: Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 6+ messages in thread
From: Jan Wieck @ 2002-06-07 14:20 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Richard Huxton wrote:
> I've been happily using statements like
> UPDATE a SET flag=1 FROM b WHERE a.id=b.id AND b.foo='x';
>
> While PG's FROM extension makes life simple, I can't believe there's not a way
> to do an update on a join using standard SQL. The two options I can think of
> are:
>
> 1. using a sub-select
> UPDATE a SET flag=1 WHERE a.id IN (SELECT id FROM b WHERE b.foo='X');
> Which is fine, but no good for mysql, hits PG's speed issue with IN and a bit
> clumsy for more complicated examples.
>
> 2. building an updatable view.
If your goal is to gain better portability for your app, go
with the subselect. Updatable views, especially if they do
joins, are far less supported (if at all).
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE ... FROM vs standard SQL
@ 2002-06-07 15:20 Josh Berkus <josh@agliodbs.com>
parent: Richard Huxton <dev@archonet.com>
2 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2002-06-07 15:20 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
Richard,
> I've been happily using statements like
> UPDATE a SET flag=1 FROM b WHERE a.id=b.id AND b.foo='x';
>
> While PG's FROM extension makes life simple, I can't believe there's
> not a way
> to do an update on a join using standard SQL. The two options I can
> think of
I don't understand why you're worried about this. The lack of UPDATE
.. FROM is widely regarded as an omission by the ANSI committee, and
most SQL RDBMS support it, including MS SQL Server and Oracle. I'm not
sure about MySQL, but MySQL doesn't support sub-selects either.
Is there a database you're tinking of that sticks to the strict SQL92
definitions? OpenBase, maybe?
-Josh Berkus
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE ... FROM vs standard SQL
@ 2002-06-07 16:05 Richard Huxton <dev@archonet.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2002-06-07 16:05 UTC (permalink / raw)
To: pgsql-sql
On Friday 07 Jun 2002 4:20 pm, Josh Berkus wrote:
> > While PG's FROM extension makes life simple, I can't believe there's
> > not a way
> > to do an update on a join using standard SQL.
>
> I don't understand why you're worried about this. The lack of UPDATE
> .. FROM is widely regarded as an omission by the ANSI committee, and
> most SQL RDBMS support it, including MS SQL Server and Oracle. I'm not
> sure about MySQL, but MySQL doesn't support sub-selects either.
Tell me about it. Where I'm using MySQL on client web-servers I find myself
porting the data into PG to massage it then putting the results back into
MySQL. One of the things missing from the various reviews/comparisons on the
web is any actual comparison of usage for DBA tasks (as opposed to handling a
message-board app etc)
> Is there a database you're tinking of that sticks to the strict SQL92
> definitions? OpenBase, maybe?
Nope - mostly work with PG/MySQL/MS-SQL/Access. I use all the features at the
psql prompt, but like to stick to the standard for app-generated queries.
Thanks Josh
- Richard Huxton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE ... FROM vs standard SQL
@ 2002-06-07 16:38 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2002-06-07 16:38 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On Fri, 7 Jun 2002, Richard Huxton wrote:
> I've been happily using statements like
> UPDATE a SET flag=1 FROM b WHERE a.id=b.id AND b.foo='x';
>
> While PG's FROM extension makes life simple, I can't believe there's not a way
> to do an update on a join using standard SQL. The two options I can think of
> are:
>
> 1. using a sub-select
> UPDATE a SET flag=1 WHERE a.id IN (SELECT id FROM b WHERE b.foo='X');
> Which is fine, but no good for mysql, hits PG's speed issue with IN and a bit
> clumsy for more complicated examples.
Well, on PG you might want to try some EXISTS form, but I think this is
the way you're probably intended to do it under SQL92 at least.
> 2. building an updatable view.
Well, AFAICS this is mostly a special case of the above. It looks to me
that updatable views can't have multiple tables in the from clause so
you'd have to write the select with a subquery anyway (and I'm not 100%
sure that's correct either).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE ... FROM vs standard SQL
@ 2002-06-07 18:50 Josh Berkus <josh@agliodbs.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2002-06-07 18:50 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
Richard,
> Tell me about it. Where I'm using MySQL on client web-servers I find myself
> porting the data into PG to massage it then putting the results back into
> MySQL. One of the things missing from the various reviews/comparisons on the
> web is any actual comparison of usage for DBA tasks (as opposed to handling
a
> message-board app etc)
But MySQL does handle message-baord apps very well. If only MySQL AB and
NuSphere would stick to that and not try to poach in transactional database
space ...
> Nope - mostly work with PG/MySQL/MS-SQL/Access. I use all the features at
the
> psql prompt, but like to stick to the standard for app-generated queries.
Well, this will work on any database with *full* SQL92 support:
UPDATE tablea SET field3 = (SELECT field5
FROM tableb
WHERE tableb.field2 = tablea.field1);
... and won't be as slow as an IN query. Quite.
However, MS SQL 7.0 does not support correlated subqueries outside of the
WHERE clause (I'm not sure about SQL 2000). For MS Access, well, forget
using subqueries at all unless you have all day ...
And for MySQL, you're hosed. Tell your clients they need a different
database.
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-06-07 18:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-07 10:33 UPDATE ... FROM vs standard SQL Richard Huxton <dev@archonet.com>
2002-06-07 14:20 ` Jan Wieck <janwieck@yahoo.com>
2002-06-07 15:20 ` Josh Berkus <josh@agliodbs.com>
2002-06-07 16:05 ` Richard Huxton <dev@archonet.com>
2002-06-07 18:50 ` Josh Berkus <josh@agliodbs.com>
2002-06-07 16:38 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox