Update stmt with an Table alias?

[John]

Hi all,

I am doing the change from having worked in Oracle for a long time to
MS SQL server and am frustrated with a couple of simple SQL stmt's. Or
at least they have always been easy.

The SQL is pretty straightforward . I am updating a field with a Max
effective dated row criteria. (PepopleSoft app)

update PS_JOB as A set BAS_GROUP_ID = ' '
where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')
and EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = A.EMPLID)

This stmt is not working. I am getting an error on the keyword 'as'. I
have tried:

update PS_JOB A set...
update PS_JOB from PS_JOB A set...

Same result, error on 'A' or error on 'from'.

I also tried to add the table alias to the sub query, which
technically worked, but with wrong data result.

So my question comes down to: How do I use a table alias in an update
statement in MS SQL server?

I worked around this by creating a temp table. But that does not
fulfill my curiosity, nor is it an ideal solution.

Thanks a lot,

-OK

[John Gilson]

Re: Update stmt with an Table alias?

"John" <oknude@yahoo.c om> wrote in message news:efedb1f7.0 405200521.5727d ecf@posting.goo gle.com...[color=blue]
> Hi all,
>
> I am doing the change from having worked in Oracle for a long time to
> MS SQL server and am frustrated with a couple of simple SQL stmt's. Or
> at least they have always been easy.
>
> The SQL is pretty straightforward . I am updating a field with a Max
> effective dated row criteria. (PepopleSoft app)
>
> update PS_JOB as A set BAS_GROUP_ID = ' '
> where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')
> and EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = A.EMPLID)[/color]

Alias the table name in the subquery and refer to the table to be updated explicitly.
The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.

update PS_JOB
set BAS_GROUP_ID = ' '
where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')
and EFFDT = (select max(EFFDT) from PS_JOB AS J
where PS_JOB.EMPLID = J.EMPLID)

--
JAG
[color=blue]
> This stmt is not working. I am getting an error on the keyword 'as'. I
> have tried:
>
> update PS_JOB A set...
> update PS_JOB from PS_JOB A set...
>
> Same result, error on 'A' or error on 'from'.
>
> I also tried to add the table alias to the sub query, which
> technically worked, but with wrong data result.
>
> So my question comes down to: How do I use a table alias in an update
> statement in MS SQL server?
>
> I worked around this by creating a temp table. But that does not
> fulfill my curiosity, nor is it an ideal solution.
>
> Thanks a lot,
>
> -OK[/color]

[Erland Sommarskog]

Re: Update stmt with an Table alias?

[posted and mailed, please reply in news]

John (oknude@yahoo.c om) writes:[color=blue]
> The SQL is pretty straightforward . I am updating a field with a Max
> effective dated row criteria. (PepopleSoft app)
>
> update PS_JOB as A set BAS_GROUP_ID = ' '
> where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')
> and EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = A.EMPLID)
>
> This stmt is not working. I am getting an error on the keyword 'as'. I
> have tried:
>
> update PS_JOB A set...
> update PS_JOB from PS_JOB A set...
>
> Same result, error on 'A' or error on 'from'.
> ...
> So my question comes down to: How do I use a table alias in an update
> statement in MS SQL server?[/color]

It's very simple. You just take a SELECT statment, rip the SELECT part
out of it, tack on UPDATE-SET, and here you go:

UPDATE PS_JOB
SET BAS_GROUP_ID = ' '
FROM PS_JOB A
WHERE A.EMPL_STATUS IN ('D', 'L', 'R', 'S', 'T')
and A.EFFDT = (select max(EFFDT)
from PS_JOB B
where B.EMPLID = A.EMPLID)

JAG was wrong when he said you cannot do this in MS SQL Server, but he
is right on ANSI SQL. The fact that most RDBMSs support alias on the
base table in an UPDATE, is a clear fact that ANSI just has it all
wrong, and what you get is different syntax in different places.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Update stmt with an Table alias?

>> The fact that most RDBMSs support alias on the base table in an
UPDATE, is a clear fact that ANSI just has it all wrong, and what you
get is different syntax in different places. <<

Actually, ANSI got it right and the various products that allows an
alias in the UPDATE give slightly different answers because they
violated standards.

Not allowing correlation names in the UPDATE clause avoids 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.

Do you think that INSERT INTO ought to allow an alias, too?

[Erland Sommarskog]

Re: Update stmt with an Table alias?

--CELKO-- (jcelko212@eart hlink.net) writes:[color=blue]
> Actually, ANSI got it right and the various products that allows an
> alias in the UPDATE give slightly different answers because they
> violated standards.[/color]

The fact that so many provides what ANSI failed to provide, it's a very
strong indication that ANSI failed to address common needs.
[color=blue]
> Not allowing correlation names in the UPDATE clause avoids 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.[/color]

I would assume that most SQL programmers does not really care about this
fine print. We are not in the field for the purity of relational
algebra but to provide customers solutions. Of course, having to
repeat a table name like Orders without an alias is not big deal,
but when you table is called instrumentclear ingmarketplaces , it's becomming
a necessity.
[color=blue]
> Do you think that INSERT INTO ought to allow an alias, too?[/color]

I never refer back to the instance of the table that is in the INSERT
clause so what would I use it for?

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Serge Rielau]

Re: Update stmt with an Table alias?

Pitty I seem to be misisng teh
--CELKO-- wrote:
[color=blue][color=green][color=darkred]
>>>The fact that most RDBMSs support alias on the base table in an[/color][/color]
>
> UPDATE, is a clear fact that ANSI just has it all wrong, and what you
> get is different syntax in different places. <<
>
> Actually, ANSI got it right and the various products that allows an
> alias in the UPDATE give slightly different answers because they
> violated standards.[/color]
I didn't know that. Do you have an example for the divergence in the
semantics?[color=blue]
>
> Not allowing correlation names in the UPDATE clause avoids 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.[/color]
That is certainly one side of the picture. The other side however is the
desire to use views. I always find the standard very schizoprenic with
this respect. After all I can DELETE, UPDATE, INSERT (and MERGE) with a
view as a target. The standard goes through very painful motions to
explain that modifying a view really modifies the underlying set of
tables. I do not see how updating through a correlation name or through
a query (as supported by Oracle and DB2) is any worse.
In fact updating through a query requires a correlation name.
[color=blue]
> Do you think that INSERT INTO ought to allow an alias, too?[/color]
But I cannot reference the target of an insert anywhere within it's
scope in the first place. Allowing a correletion name would be fine, but
useless.
UPDATE or DELETE are different animals alltogether because they
encourage reference of the target table in the left hand side of the SET
as well as in the WHERE clause.


Update FROM, of course has it's own set of problems which is a different
topic.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Joe Celko]

Re: Update stmt with an Table alias?

>> would assume that most SQL programmers does not really care about
this fine print. We are not in the field for the purity of relational
algebra but to provide customers solutions. <<

I'd like to think that most programmers want to have a few general,
consistent rules in the semantics of any programming language that they
can rely on. Remember the seven forms that a subscript expression could
have in FORTRAN II? They were all replaced by the single rule "anything
that returns an integer" and nobody cried.
[color=blue][color=green]
>> Of course, having to repeat a table name like Orders without an alias[/color][/color]
is not big deal, but when you table is called
instrumentclear ingmarketplaces , it's becoming
a necessity. <<

Could be worse; COBOL programmers have to write huge sentences with very
long data element names and yet somehow 78% of the world's code is still
written in COBOL. Are SQL programmers weaker, lazier and not able to
type as well as COBOL programmers?

I use a text editor with a global placement function myself. Now I'd
like to get a tool that expands "SELECT *" and checks the names against
the schema information tables before I go to production code.
[color=blue][color=green]
>> I never refer back to the instance of the table that is in the INSERT[/color][/color]
clause so what would I use it for? <<

Of course you can! Making up some syntax, what would this mean?

INSERT INTO Foobar (x,y) AS F1 (a,b)
VALUES (F1.b, Foobar.x);

or reference Foobar in a SELECT clause and make a few self-references
until you have complete mess.

Another point, getting back to the UPDATE, what does this mean?

UPDATE Foobar AS F1(a,b,c)
SET x = 42,
a = 24;

Or do you have to set up one set of rules for alias in an UPDATE,
different rules for alias in INSERT INTO, another rule for alias in
DELETE FROM and more rules for SELECT?

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Serge Rielau]

Re: Update stmt with an Table alias?

Joe Celko wrote:
[color=blue][color=green][color=darkred]
>>>I never refer back to the instance of the table that is in the INSERT[/color][/color]
>
> clause so what would I use it for? <<
>
> Of course you can! Making up some syntax, what would this mean?
>
> INSERT INTO Foobar (x,y) AS F1 (a,b)
> VALUES (F1.b, Foobar.x);[/color]
Joe are you 100% sure you're not off on this one?
For all I know (which may not be much) a couple of problems:
INSERT INTO Foobar(x, y) VALUES (x, y) is illegal already.
With or without correlation name. The target table is not in scope.

But let's get back to the roots here:
SELECT x FROM Foobar AS F1(a, b, c)
is illegal in the SQL Standard as well. And so is
SELECT F1.x FROM Foobar AS F1(a, b, c).
Correlation names always eclipse the original names.
SELECT x FROM (SELECT x AS a FROM Foobar), ......
And these rules hold true throughout. So they work fine for UPDATE,
DELETE and MERGE.

FWIW, here is what DB2 does:
db2 => create table T(c int);
DB20000I The SQL command completed successfully.
db2 => select c from T AS X;
C
-----------

0 record(s) selected.

db2 => select c from T AS X(y);
SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703

db2 => select T.c from T AS X(y);
SQL0206N "T.C" is not valid in the context where it is used.
SQLSTATE=42703

db2 => select c from (select c as y from T) as X;
SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703

db2 => insert into T(c) values c;
SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703

db2 => insert into T(c) values T.c;
SQL0206N "T.C" is not valid in the context where it is used.
SQLSTATE=42703

db2 => update T as X SET T.c = 5;
SQL0206N "T.C" is not valid in the context where it is used.
SQLSTATE=42703

db2 => update T as X SET X.c = 5;
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of
a query is an empty table. SQLSTATE=02000

db2 => update T as X(y) SET X.c = 5;
SQL0206N "X.C" is not valid in the context where it is used.
SQLSTATE=42703

db2 => update T as X(y) SET X.y = 5;
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of
a query is an empty table. SQLSTATE=02000

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Erland Sommarskog]

Re: Update stmt with an Table alias?

Joe Celko (jcelko212@eart hlink.net) writes:[color=blue]
> I'd like to think that most programmers want to have a few general,
> consistent rules in the semantics of any programming language that they
> can rely on.[/color]

Yes, and this why the UPDATE statement is so great in SQL Server.
You can build it just like you build your SELECT statement. If you
want to test you UPDATE statement, you can easily change it into a
SELECT and see that you affect the rows you intend to.

ANSI, on the other hand, offers a completely different paradigm, which
forces you to think differently and where you easily can go wrong.
[color=blue]
>Remember the seven forms that a subscript expression could
> have in FORTRAN II?[/color]

No, the only Fortran dialect I've learnt is Fortran 77.
[color=blue]
> Could be worse; COBOL programmers have to write huge sentences with very
> long data element names and yet somehow 78% of the world's code is still
> written in COBOL. Are SQL programmers weaker, lazier and not able to
> type as well as COBOL programmers?[/color]

It is not about typing. It is about verbosity. Too long names repeated
over and over again and you can't see the wood for the trees.
[color=blue]
> Of course you can! Making up some syntax, what would this mean?
>
> INSERT INTO Foobar (x,y) AS F1 (a,b)
> VALUES (F1.b, Foobar.x);[/color]

Yeah, would that mean? Before you throw in the alias, tell us what
that Foobar.x means in the VALUES clause. (Maybe it is in ANSI-SQL,
but I don't know that SQL dialect.)
[color=blue]
> Or do you have to set up one set of rules for alias in an UPDATE,
> different rules for alias in INSERT INTO, another rule for alias in
> DELETE FROM and more rules for SELECT?[/color]

Actually, in MS SQL Server it is plain and simple. No alias for INSERT,
and then the same rules for SELECT, UPDATE and DELETE with some small
additions for the target table and the SET clause. The SET clause
is simple: there is no need for aliases anyway. For UPDATE and DELETE
you can say either "UPDATE alias" or "UPDATE tbl". In SQL 2000, you
only need "UPDATE alias", when you self-join. (In SQL7 and 6.5 for
some reason you needed "UPDATE alias" when you use ANSI-style joins.)

I can't speak for other variations on the theme, since SQL Server is
the only engine I know.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Joe Celko]

Re: Update stmt with an Table alias?

>> Yeah, would that mean? Before you throw in the alias, tell us what
that Foobar.x means in the VALUES clause. (Maybe it is in ANSI-SQL, but
I don't know that SQL dialect.) <<

Unh? T-SQL is a dialect; Standard SQL is the, well, THE Standard.
You're the one with the funny accent. It is legal to write:

INSERT INTO Foobar (x,y) VALUES (Foobar.a, Foobar.b);
or even:
INSERT INTO Foobar (x,y) VALUES (a, b);

because Foobar is in the outermost scope of the INSERT INTO statement.
Of course, you can get a cardinality violation at run time and have to
use a table constructor instead of a row constructor.

INSERT INTO Foobar (x,y)
SELECT a, b FROM Foobar;
[color=blue][color=green]
>> Actually, in MS SQL Server it is plain and simple. No alias for[/color][/color]
INSERT, and then the same rules for SELECT, UPDATE and DELETE with some
small additions for the target table and the SET clause. <<

Ah yes, exactly the same rules, except where they are totally different
:)

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Serge Rielau]

Re: Update stmt with an Table alias? Yes, quote from SQL Standard:

Nonsense. Anyway below should settle UPDATE case.
Th burden of proof that the target columns of INSERT can be referenced
in the insert souce> lies on you. It's your claim :-)

Here is the SQL2003 standard Foundation excerpt for UPDATE. This is not new:

14.11 <update statement: searched>
Function
Update rows of a table.
Format
<update statement: searched> ::=
UPDATE <target table> [ [ AS ] <correlation name> ]
SET <set clause list>
[ WHERE <search condition> ]
Syntax Rules
1) Let TN be the <table name> contained in <target table>;
let T be the table identified by TN. T shall be an
updatable table.
2) T is the subject table of the <update statement: searched>.
3) TN shall not identify an old transition table or
a new transition table.
4) Case:
a) If <correlation name> is specified,
then let CN be that <correlation name>.
b) Otherwise, let CN be the <table name> contained in
<target table>. CN is an exposed <table or query name>.
5) The scope of CN is <set clause list> and <search condition>.

And that's ths end of that!

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Erland Sommarskog]

Re: Update stmt with an Table alias?

Joe Celko (jcelko212@eart hlink.net) writes:[color=blue]
> Unh? T-SQL is a dialect; Standard SQL is the, well, THE Standard.
> You're the one with the funny accent.[/color]


T-SQL is one dialect of SQL. ANSI SQL is another. The latter may aspire
to be *the* standard, but at best is a smorgasbord from which their
vendor may pick new constructs for their dialects - but they may also
make up their own syntax.

That said, there is a lot of thought put into ANSI SQL, and many of
the constructs have proven useful. However, when it comes to the aim
to be a single standard, ANSI SQL still has a long way to go.
[color=blue]
>It is legal to write:
>
> INSERT INTO Foobar (x,y) VALUES (Foobar.a, Foobar.b);
> or even:
> INSERT INTO Foobar (x,y) VALUES (a, b);[/color]

Maybe it is legal in ANSI SQL, but I still don't know what it means.
Possibly I can guess from your other example that it means the same
as:
[color=blue]
> INSERT INTO Foobar (x,y)
> SELECT a, b FROM Foobar;[/color]

I'm not really sure I see the point of reinserting all columns in a
table a second time, and without an IDENTITY column for PRIMARY KEY,
it's going to blow up anyway.

But of course, if the above syntax means anything, applying an alias
would not be a strange thing, but only a natural extension of the
madness.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Serge Rielau]

Re: Update stmt with an Table alias?

Erland Sommarskog wrote:
[color=blue]
> Joe Celko (jcelko212@eart hlink.net) writes:
>[color=green]
>>Unh? T-SQL is a dialect; Standard SQL is the, well, THE Standard.
>>You're the one with the funny accent.[/color]
>
>
>
> T-SQL is one dialect of SQL. ANSI SQL is another. The latter may aspire
> to be *the* standard, but at best is a smorgasbord from which their
> vendor may pick new constructs for their dialects - but they may also
> make up their own syntax.
>
> That said, there is a lot of thought put into ANSI SQL, and many of
> the constructs have proven useful. However, when it comes to the aim
> to be a single standard, ANSI SQL still has a long way to go.[/color]
I don't believe ANSI SQL has a long way to go. It's worse. I fear ANSI
SQL has lost the battle. It seems like most vendors only follow the
standard when they implement new features which the SQL Standard has
already dealt with (like "common table expression" in Oracle and Yukon)
But when it comes to truly new function vendors choose to ship first and
maybe later bring the beast in.
It's a highly politcal game.
On the other hand, once a gorilla like SAP or Peoplesoft enters the
arena things start to happen because these guys insist.

Joe's problem in this thread seems to be that he doesn't hear his own
accent however. One must consult the book on occasion to stay centered ;-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[--CELKO--]

Re: Update stmt with an Table alias?

>> ANSI SQL is another. The latter may aspire to be *the* standard,
but at best is a smorgasbord from which their vendor may pick new
constructs for their dialects - but they may also make up their own
syntax. <<

Unfortunately true. At least there is some hope that the vendors will
tend toward the smorgasbord instead of inventing their own.
[color=blue][color=green]
>> I don't believe ANSI SQL has a long way to go. It's worse. I fear[/color][/color]
ANSI SQL has lost the battle ... It's a highly politcal game. On the
other hand, once a gorilla like SAP or Peoplesoft enters the arena
things start to happen because these guys insist. <<

The real political problem goes back to campaign contributions made
during the Clinton era. Clinton dropped the FIPS-127 conformance test
programming, thus destroying a legally enforcible definition of the
standard. It was very nice when the US Government and the NIST labs
were the "Big Gorilla" in the game. NIST was also active on the
original ANSI X3H2 committee, so there was a nice feedback loop
between the tests and the real products.
[color=blue][color=green]
>> Joe's problem in this thread seems to be that he doesn't hear his[/color][/color]
own accent however. <<

Hey, Chris Date is the "lone voice in the wilderness" screaming how
bad SQL is because it is not relational enought, and I am the other
lunatic screaming that the vendors are not SQL enough! Do you think
it is easy being a religious fanatic <g>?

[Serge Rielau]

Re: Update stmt with an Table alias?

What more can I say? It's the tooth.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab