If I have two tables with the following schema:

CREATE TABLE TEST1(iID int primary key, sChar char(1))
CREATE TABLE TEST2(iID int primary key, sChar char(1))

And fill them with data as follows:

insert TEST1(iID,sChar) VALUES (1,'a')
insert TEST1(iID,sChar) VALUES (2,'b')

insert TEST2 (iID,sChar) VALUES (1,'c')
insert TEST2 (iID,sChar) VALUES (2,'d')


What would be the portable way to update TEST2 such that TEST2.sChar =
TEST1.sChar where TEST2.iID = TEST1.iID. To clarify, the end result of the
update statment should be table 2 with the following recordsets:

iID sChar
1 'a'
2 'b'


I've done my due diligence in searching for an answer on this one but every
answer I have seen involves UPDATE..FROM which is proprietary. The only
solution I can come with on my own would be to either use a cursor or a
while loop.


Cheers,

Tyler

"Tyler Hudson" <TylerH (AT) Spam (DOT) MeNOTallpax.com> wrote

Use a correlated subquery:

Update Test2
set sChar =
(Select test1.sChar
from test1
where test1.iID = test2.iID ) ;

--
Paul Horan
Sr. Architect VCI
Springfield, Mass

Thank you very much. I don't know why I had such difficulty finding that
info...let's just chalk it up to user-error. Anyway, thank you again.
"-P-" <ent_must_die (AT) hotmail (DOT) DOTcom> wrote

The standard syntax is INSERT INTO, not INSERT.

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.

Just another example of useless noise words in the SQL standard.

On the contrary it's one of the few useful extensions of T-SQL.

Don't pretend that the standard doesn't change, it's a vendor driven
pile of you know what. And when the SQL standards committee decides to
change it, you will have to do a re-write.

In what version of the standard? In which vendor's dialect? The
correct syntax for an update statement depends on the DBMS you are
using, not some unrealistic approximation designed by a committee.

<snipped lengthy off-topic discourse>

Are you a big fan of APL or LISP? They are much, much more compact.
And much, much less readable, too. You must hate the fact that about
78% of the commercial programs in the world are still in COBOL. Have
you looked at any of the research on how much a few noise words help
in reading (and therefore in maintaining) code?

Many years ago, there was an article in CACM on translating COBOL into
Arabic at the grammar level. That is not just replacing English words
with Arabic, but arranging the word order in a COBOL compiler to match
the word order of Arabic.


Which version of this syntax do you find useful? Did you miss the
part about the random ordering of their extensions? Or do you
consider unpredictable code to be a good thing?? Let me quote it:

"The results of an UPDATE statement are undefined if the statement
includes a FROM clause that is not specified in such a way that only
one value is available for each column occurrence that is updated (in
other words, if the UPDATE statement is not deterministic). For
example, given the UPDATE statement in the following script, both rows
in table s meet the qualifications of the FROM clause in the UPDATE
statement, but it is undefined which row from s is used to update the
row in table t. <<example code>>"

This replaces a prior behavior in the Sybase and Ingres family where
the UPDATE.. FROM would do multiple updates, one for each joined row
in the second table.

In older versions of Sybase/SQL Server, if a base table row is
represented more than once in the embedded query, then that row is
operated on multiple times instead of just once. A total violation of
relational principles. Here is a quick example:

CREATE TABLE T1 (x INTEGER NOT NULL);
INSERT INTO T1 VALUES (1);
INSERT INTO T1 VALUES (2);
INSERT INTO T1 VALUES (3);
INSERT INTO T1 VALUES (4);

CREATE TABLE T2 (x INTEGER NOT NULL);
INSERT INTO T2 VALUES (1);
INSERT INTO T2 VALUES (1);
INSERT INTO T2 VALUES (1);
INSERT INTO T2 VALUES (1);

Now try to update T1 by doubling all the rows which have a match in
T2; The FROM clause gives you a CROSS JOIN.

UPDATE T1
SET T1.x = 2 * T1.x
FROM T2
WHERE T1.x = T2.x;

SELECT * FROM T1;

original current
x x
==== ====
16 2
2 2
3 3
4 4

The FROM clause gives you a CROSS JOIN, so you get a series of actions
on the same row (1 => 2 => 4 => 8 => 16). These are pretty simple
examples, as you can see, but you get the idea. There are subtle
things with self-joins and the diseased mutant T-SQL syntax which can
hang you in loops by changing things, or you can have tables which are
dependent on the order of the rows for their results, etc.

SQL Server and Sybase used different "fixes" this problem in later
versions of their products. Sybase did a "hidden SELECT DISTINCT" in
the implied query and SQL Server gets unpredictable. These changes
broke a lot of code, just like the deprecation of *= outer joins.

Standard SQL is very consistent and very clear about aliases, views
and derived tables, as well as a highly orthogonal language.

If the UPDATE clause could take an alias, according to the Standard
SQL model, you would create a copy of the contents of that base table
under the alias name, then update that copy, and delete it when the
statement was over -- in effect doing nothing to the base table.

If the UPDATE clause could take a FROM clause, according to the
Standard SQL model, you would create a nameless copy of the result set
of the table expression, then update that copy, and delete it when the
statement was over -- in effect doing nothing to the base tables. If
you gave the result set of the table expression a name, the same rules
would apply and you would still be doing nothing to the base tables.

Since this syntax is so proprietary, inconsistent with the standard
model and ambiguous, why does it exist? In the original Sybase
product, the *physical* model made this "extension" relatively easy to
implement and there were no standards or even a good understanding of
the relational model. This is how a sequential file system would do
an update based on a tape merge -- **shudder**.

pile of you know what. And when the SQL standards committee decides to
change it, you will have to do a re-write. <<

The standards DO change, but they change one and only one way. Vendor
extension change many different ways. Which is why the original
poster is having problems.

SQL-92 and above; everyone that I can think of has it at their core,
even if they added extensions. The reason they agree has to do with
FIPS-127 conformance testing (do you want a federal contract?) and the
original System-R SQL language.

<<

Wrong. A standard is very specific and not at all approximate. And
wasn't that evil committee made up of those same vendors? The real
shame is that nobody seems to be working on ROW() constructors for
their UPDATE statements.

There's a big difference between readable and verbose. Let's translate
the SQL standard syntax for the insert statement into English:

insert into T select 5 as ID from <some vendor extension for a feature
that should have been in the standard in the first place>

Which reads:

insert into That table This data.

Uh, what? I would much prefer a syntax like:

insert table { row { 5 ID } } into T;

Which reads:
Insert this data into that table

No useless noise words, and even more readable than the SQL.

Beavers and ducks.

The version that let's me update a set of data restricted by a join
without having to use a subselect, i.e. the more readable version of
the statement.

Talk about violation of relational principles. The examples you give
have duplicates. Of course the behavior is undefined, what did you
expect? How can the system decide which row to update? I suggest you
use keys on your tables.

Orthogonal language? You can't be serious. We have a saying:
The only consistent thing about SQL is that it is inconsistent.

I refer you to the numerous critiques of the SQL language in the
literature. SQL is a shining example of how *not* to design a
language.

You are missing the fundamental point that the expression in question
is an lvalue, not an rvalue.

Look it's really quite simple. It's a useful extension because it
allows me to formulate updates and deletes that would otherwise
require convoluted sub-selects, or worse, cursors to perform. The
blame for the non-deterministic behavior lies with the SQL language
itself for allowing duplicates in the first place, not with the
extension.

What world do you live in? The SQL Standard is not a standard. The
specification is *internally* inconsistent. It is *impossible* to
implement completely. No vendor even comes close (or even has plans
to) and most systems don't even implement the core of the standard.
Even the ones that do have so many nuances and caveats that they might
as well be different languages altogether.

If SQL was really a standard language, I could write an application
against one system, and port it without change to another system. This
is the promise of standardization that SQL has failed so dismally to
achieve.

What? If I'm writing an application against a given backend system, it
changes in one way, namely, the way the vendor changed it. The
original poster is not having problems with vendor changes, re-read
the post. The problem is that the SQL language is counter-intuitive.
Why should I have to specify what can only be called a join using a
subselect, just because I want to perform an update. And by the way,
what happens if more than one column is involved?

Has it at their core? Most systems don't even implement nested from
clauses, the only thing that makes SQL even workable as a data
manipulation language.

Wrong. If I write my update statement according to the standard, I
cannot expect that update statement to work against an arbitrary
SQL-based system. It really doesn't matter what the standard says,
because at the end of the day, I still have to code against a system
that has a specific syntax. *That* is the correct syntax for my update
statement.

<snip lengthy discussion>

I will assume from your silence on these issues that you agree with me
and have conceeded the point that from clause updates are a useful
extension to the SQL language.

jingleheimerschmitt (AT) hotmail (DOT) com (John Jacob) wrote:

What about assuming that he did not see your post? It is not as
if USENET has guaranteed delivery.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

extension to the SQL language. <<

I just posted a long essay on how the *several* different UPDATE..
FROM .. syntaxes are absolute crap; they violate the consistency of
the FROM clause as a tabl expression creation; the results are
unpredictable from product to product and from one release to another
of the same product.

Is that loud enough for you to hear me?