Alias has confused me.

I'm trying to learn how to make and use aliases for two tables in in
this update statement:

ALTER PROCEDURE dbo.UpdateStatus
AS UPDATE dbo.npfields
SET Status = N'DROPPED'
FROM dbo.npfields NPF, dbo.importparsed IMP
LEFT JOIN IMP
ON (NPF.pkey =  IMP.pkey)
WHERE (IMP.pkey IS NULL) AND
((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =
N'ERR3'))


I thought I could define the aliases in the FROM statement.

I'm using Access as a front end to SQL server if that makes a
difference in the queries.

A couple of quick notes...

- In the future please post DDL statements to create your tables as
well as INSERT statements to fill them with sample data. It's also
usually helpful to provide a sample of what you expect to see in your
solution.

- For problems where you are encountering an error, please *provide the
exact error message*. It's very hard to try to help solve someone's
problem when you don't know what the problem is. Imagine taking your
car to a mechanic, saying, "There's something wrong with my car, please
fix it." and then leaving.

Yes, you can (and should usually) define aliases in the FROM statement.
In an UPDATE statement, if you use aliases in the FROM/JOIN clause(s)
then you need to use that alias in the UPDATE clause. So, you should
have:

UPDATE NPF
SET Status = ...

The reason that this is required is because you don't always have to
update the table in the FROM clause - it can be one of the tables in
your JOIN clause. You could also have the same table appear twice in
your query, so SQL would not know which one you wanted to actually
update. For example:

UPDATE MyTable
SET child_flag = 1
FROM MyTable T1
INNER JOIN MyTableT2 ON T2.parent_id = T1.id

Which rows do I really want to update? The rows using T2 or using T1?

HTH,
-Tom.

Hi shumaker,

On 15 Apr 2005 10:08:27 -0700, shumaker@cs.fsu.edu wrote:

>I'm trying to learn how to make and use aliases for two tables in in
>this update statement:
(snip)

In addition to Thomas' remarks, some more thoughts:

>FROM dbo.npfields NPF, dbo.importparsed IMP
>LEFT JOIN IMP

This is the part that will cause an error. The parser will interpret
this as an "old-style" join between npfields (aliased as NPF) and
importparsed (aliased as IMP), which is then left joined to a third
table, named IMP (which probably does not exist in your DB). The syntax
you need is

FROM dbo.npfields AS npf
LEFT JOIN dbo.importparsed AS imp

(Note that I included the optional AS keyword - IMO, this makes it
easier to see that you're using aliases).


Also, remember that the UPDATE ... FROM is proprietary syntax that won't
port to any other database. And it has some side effects that can bite
you pretty bad if you're not aware of them (especially if rows in the
table to be updated can be joined to more than one row in the other
tables).

This syntax does have it's uses, but you should consider very carefully
when you use it, and stick to ANSI standard syntax whenever possible. In
the case of this specific query, I'd prefer this version:

UPDATE dbo.npfields
SET    Status = N'DROPPED'
WHERE  Status IN (N'ERR1', N'ERR2', N'ERR3')
AND NOT EXISTS (SELECT *
FROM   dbo.importparsed AS imp
WHERE  imp.pkey = npfields.pkey)


>I'm using Access as a front end to SQL server if that makes a
>difference in the queries.

That depends. For pass-through queries, Access just hands the query text
over to SQL Server; SQL Server then executes the query and passes the
results back to Access. These queries have to use SQL that SQL Server
understands (most of the ANSI standard, plus Transact-SQL extensions).

For "normal" (i.e. not pass-through) queries, Access itself will execute
the query, fetching rows from SQL Server, joining and processing on the
client (and, in the case of an UPDATE, passing changes back to SQL
Server). These queries have to use Jet-SQL, that unfortunately is quite
different from both ANSI and T-SQL.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

You are a student so you get the full lecture!

Why are you writing in a dangerous dialect? You do know that this
syntax can cause cardinality violations to go undetected.   Try using
Standard SQL, instead. My guess is that you want something like this:

UPDATE NPfields
SET foobar_status = 'DROPPED'
WHERE foobar_status IN ('ERR1', 'ERR2', 'ERR3') -- current values
AND EXISTS  -- have a match in the other table
(SELECT *
FROM ImportParsed AS IMP
WHERE IMP.pkey = NPfields.pkey);

Since status is too vague to be a data element name, I changed it; you
will want something more meaningful.
 

There is no FROM clause in an UPDATE.  It wold make no senses in the
SQL model.  An alais has to act as if it is materialized, so in
Standard SQL you would be changing a working table that disappears at
the end of statement.
 
difference in the queries. <<

ACCESS is a total mess; can you get a better front end at your school?

This is at work.  They aren't really familier with anything other than
Access, and I want to have it setup so that if I ever leave they will
be able to make modifications themselves.

Is there some other frontend you would suggest?  It needs to be
something that requires little or no programming knowledge for the sake
of being useable by future employees who will likely not have
programming knowledge.  I could probably get just about anything I
asked for.

I've got it setup to use SQL Server instead of the Access database
files because they are prone to corruption.

Sorry about vagueness in my post.  I really am new to SQL, and the only
examples of UPDATE statements I could find on the net were fairly
simplistic and referenced only a single table.

I get confused about the flow control of the statement.  I get the
impression that parts of the SQL statement are executed, and return a
set of records that are operated on by the next part of the statement.


I am trying to update all the records in a table where the primary key
of the record is not found in a second table.

I will look over your posts and if I still am having trouble I will
post again with better detail.

Thanks all.

Edit:
I am trying to update all the records in a table where the primary key
of the record is not found in a second table, and the Status field of
the record == ERR1, ERR2, or ERR3

but like I said, maybe I can figure it out on my own now.