Thread: UPDATE ... FROM vs standard 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

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 #

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

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).

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

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
informationtechnology     josh@agliodbs.com   and data management solutions     (415) 565-7293  for law firms, small
businesses     fax 621-2533   and non-profit organizations.     San Francisco