Re: use And or Where to filter join table in Update statement?
From: Steve (anonymous_at_discussions.microsoft.com)
Date: 05/14/04
- Next message: Tom Moreau: "Re: Convert help needed desperately"
- Previous message: Guadala Harry: "Each Student's Most Difficult Class"
- In reply to: Joe Celko: "Re: use And or Where to filter join table in Update statement?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 13 May 2004 17:32:17 -0700
Thanks for your reply and for the theory. The subquery in
the Set clause is what I was looking for. Someone from
this group gave me the syntax I was using (not to pass the
buck), but obviously I was having a problem. Your theory
explanation did help to clear up some misconceptions I had.
Steve
>-----Original Message-----
>Please post DDL, so that people do not have to guess what
the keys,
>constraints, Declarative Referential Integrity,
datatypes, etc. in your
>schema are. What you posted is garbage.
>
>The the prefixes on the names you invented are in
involation of
>ISO-11179. You don't know that rows are not records;
fields are not
>columns; tables are not files. If an id is not required
to be unique
>then it is not an identifier by definition. You failed to
use ISO-8601
>date formats.
>
>Let's start with the basics: There is no FROM clause in
a Standard SQL
>UPDATE statement; it would make no sense. Other products
(SQL Server,
>Sybase and Ingres) also use the UPDATE .. FROM syntax,
but with
>different semantics. So it does not port, or even worse,
when you do
>move it, it trashes your database. Other programmers
cannot read it and
>maintaining it is harder. And when Microsoft decides to
change it, you
>will have to do a re-write. Remember the deprecated "*="
versus "LEFT
>OUTER JOIN" conversions?
>
>The correct syntax for a searched update statement is
>
><update statement> ::=
> UPDATE <table name>
> SET <set clause list>
> [WHERE <search condition>]
>
><set clause list> ::=
> <set clause> [{ , <set clause> }...]
>
><set clause> ::= <object column> = <update source>
>
><update source> ::= <value expression> | NULL | DEFAULT
>
><object column> ::= <column name>
>
>The UPDATE clause simply gives the name of the base table
or updatable
>view to be changed.
>
>Notice that no correlation name is allowed in the UPDATE
clause; this is
>to avoid some self-referencing problems that could
occur. But it also
>follows the data model in Standard SQL. When you give a
table expression
>a correlation name, it is to act as if a materialized
table with that
>correlation name has been created in the database. That
table then is
>dropped at the end of the statement. If you allowed
correlation names
>in the UPDATE clause, you would be updating the
materialized table,
>which would then disappear and leave the base table
untouched.
>
>The SET clause is a list of columns to be changed or
made; the WHERE
>clause tells the statement which rows to use. For this
discussion, we
>will assume the user doing the update has applicable
UPDATE privileges
>for each <object column>.
>
>* The WHERE Clause
>
>As mentioned, the most important thing to remember about
the WHERE
>clause is that it is optional. If there is no WHERE
clause, all rows in
>the table are changed. This is a common error; if you
make it,
>immediately execute a ROLLBACK statement.
>
>All rows that test TRUE for the <search condition> are
marked as a
>subset and not as individual rows. It is also possible
that this subset
>will be empty. This subset is used to construct a new
set of rows that
>will be inserted into the table when the subset is
deleted from the
>table. Note that the empty subset is a valid update that
will fire
>declarative referential actions and triggers.
>
>* The SET Clause
>
>Each assignment in the <set clause list> is executed in
parallel and
>each SET clause changes all the qualified rows at once.
Or at least
>that is the theoretical model. In practice,
implementations will first
>mark all of the qualified rows in the table in one pass,
using the WHERE
>clause. If there were no problems, then the SQL engine
makes a copy of
>each marked row in working storage. Each SET clause is
executed based
>on the old row image and the results are put in the new
row image.
>Finally, the old rows are deleted and the new rows are
inserted. If an
>error occurs during all of this, then system does a
ROLLBACK, the table
>is left unchanged and the errors are reported. This
parallelism is not
>like what you find in a traditional third-generation
programming
>language, so it may be hard to learn. This feature lets
you write a
>statement that will swap the values in two columns, thus:
>
>UPDATE MyTable
>SET a = b, b = a;
>
>This is not the same thing as
>
>BEGIN ATOMIC
>UPDATE MyTable
>SET a = b;
>UPDATE MyTable
>SET b = a;
>END;
>
>In the first UPDATE, columns a and b will swap values in
each row. In
>the second pair of UPDATEs, column a will get all of the
values of
>column b in each row. In the second UPDATE of the pair,
a, which now
>has the same value as the original value of b, will be
written back into
>column b -- no change at all. There are some limits as
to what the
>value expression can be. The same column cannot appear
more than once
>in a <set clause list> -- which makes sense, given the
parallel nature
>of the statement. Since both go into effect at the same
time, you would
>not know which SET clause to use.
>
>If a subquery expression is used in a <set clause>, and
it returns a
>single value, the result set is cast to a scalar; if it
returns an
>empty, the result set is cast to a NULL; if it returns
multiple rows, a
>cardinality violation is raised.
>
>Want to try again and get right or are you just looking a
kludge?
>
>--CELKO--
>
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>
- Next message: Tom Moreau: "Re: Convert help needed desperately"
- Previous message: Guadala Harry: "Each Student's Most Difficult Class"
- In reply to: Joe Celko: "Re: use And or Where to filter join table in Update statement?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
