473,573 Members | 2,814 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update stmt with an Table alias?

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
Jul 20 '05 #1
14 47874
"John" <ok****@yahoo.c om> wrote in message news:ef******** *************** ***@posting.goo gle.com...
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)
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
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

Jul 20 '05 #2
[posted and mailed, please reply in news]

John (ok****@yahoo.c om) writes:
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?


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
>> 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?
Jul 20 '05 #4
--CELKO-- (jc*******@eart hlink.net) writes:
Actually, ANSI got it right and the various products that allows an
alias in the UPDATE give slightly different answers because they
violated standards.
The fact that so many provides what ANSI failed to provide, it's a very
strong indication that ANSI failed to address common needs.
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.
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.
Do you think that INSERT INTO ought to allow an alias, too?


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
Pitty I seem to be misisng teh
--CELKO-- wrote:
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.

I didn't know that. Do you have an example for the divergence in the
semantics?
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. 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.
Do you think that INSERT INTO ought to allow an alias, too?

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
Jul 20 '05 #6
>> 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.
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 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.
I never refer back to the instance of the table that is in the INSERT

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!
Jul 20 '05 #7
Joe Celko wrote:
I never refer back to the instance of the table that is in the INSERT


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

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
Jul 20 '05 #8
Joe Celko (jc*******@eart hlink.net) writes:
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.
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.
Remember the seven forms that a subscript expression could
have in FORTRAN II?
No, the only Fortran dialect I've learnt is Fortran 77.
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?
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.
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);
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.)
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?


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9
>> 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;
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. <<

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!
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2843
by: Julie Paten | last post by:
**** Post for FREE via your newsreader at post.usenet.com **** Hello, I am using sql+ to try and update a table and am having some trouble. Below is a select statement with the result I want to acheive. I want to get this result by using update to update my table. I have attempted to create the update sql statement, see below.
6
34886
by: Jack Tanner | last post by:
I have two complex subqueries that I need to join. I suspect this problem is due to using aliases instead of table names, but I don't know how to work around it (temporary tables?). Please help. SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2 JOIN t1 ON t2.col = t1.col; >>> Not unique table/alias: 't1'. SELECT col FROM...
1
32342
by: Caroline | last post by:
I need to update a table by copying a column from another table (having the same structure, but on another database), from the record having the same primary key. 1 - What is the correct query? 2 - I tried copying them record by record, but the datatype is ntext, (it displays <long
2
1647
by: eight02645999 | last post by:
hi i use odbc to update a table in a database but i always get return value of -1 even though i tried to return an integer. the table is updated though .... sql = """ update table set column = 0 where col = "%s" select @@rowcount
3
6321
by: Rodríguez Rodríguez, Pere | last post by:
Hello, I think I have found a query problem when the query has an alias for a table and use alias item and table name. I ilustrate the problem with a simple table and query. prr=# create table foo (c1 int2, c2 int2); CREATE TABLE prr=# insert into foo values (1, 1);
5
10143
by: No bother | last post by:
I am using 5.0.26-NT on Windows 2000. I have need to use a reference in the outer from clause in a subquery in the select clause. Consider the following example: Select (select b.baitID from b where b.entrydate curdate()) as wantedBaitIDs from bait_tbl b; My actual need is more complex than this as part of it is a rough cross tab. If...
1
6464
by: Sharon | last post by:
Hello All, Is it possible to update Sql Table through DataGrid. I have a DataGrid which is being populated through a stored procedure, all i wanted to do is to update one field (FieldName-Authorised) which has a datatype bit through DataGrid but not sure how to go about it. Any Ideas Guys on this one, your help is greatly appreciated. ...
2
5261
by: tom1234 | last post by:
Hello I am trying to write an SQL statement that uses two derived tables / subqueries in a following way: SELECT * FROM tbl A ... INNER JOIN (SELECT * FROM tbl2 WHERE col = (SELECT MIN(col2) FROM tbl2
1
5792
by: Lanaa | last post by:
I have a problem joining two tables. mysql> SELECT VARA.VID, VARA.ANTAL, VARA.PRIS, LEVERANTOR.LEVID, LEVERANTOR.TELEFONNUMMER FROM (VARA INNER JOIN LEVERANTOR ON VARA.VID=LEVERANTOR.LEVID) INNER JOIN VARA ON VARA.VID=LEVERANTOR.LEVID); I receive the following error: ERROR 1066 (42000): Not unique table/alias: 'VARA' I am using the MySQL...
0
7784
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7704
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8031
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8073
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6424
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5600
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5294
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3734
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
1044
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.