Update Statement with a Join...
Greg Kaufman
obvious here.
I'm trying to do an update on a table, based on info in a
related joined table.
In Access, I'd simply run the following query:
UPDATE tblCities INNER JOIN tblStreets ON tblCities.City
= tblStreets.City SET tblStreets.CityID = [tblCities].
[cityID]
Using the exact same data structure with SQL Server, the
only way I could do it was by creating a view:
SELECT dbo.tblCities.CityID AS GoodID,
dbo.tblStreets.CityID AS NeedID
FROM dbo.tblCities INNER JOIN
dbo.tblStreets ON
dbo.tblCities.City = dbo.tblStreets.City
And then running the update on the view:
update vwCitiesAndStreets
SET NeedID = GoodID
This did the job. But there's got to be a way to make it
one update query, rather than making a view & then the
update on the view.
What am I missing?
Thanks in advance...
Greg
sampangi
Try
UPDATE ts
SET ts.CityID = tc.cityID
FROM tblStreets ts
INNER JOIN tblCities tc ON tc.City = ts.City
HTH,
Srinivas Sampangi
>.
>
Joe Celko
You are suffering from ACCESS Poisoning :) That language is not SQL and
that syntax makes no sense in the Standard SQL model. An UPDATE is a
statement and not a query by definition.
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.
Ergo, the clause "UPDATE Cities INNER JOIN Streets ON Cities.City =
Streets.City" would ceate a working table without a name, update it and
drop it, -- if it were valid at all. Pretty useless, which is why it is
not part of real SQL.
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.
UPDATE Streets
SET streets.city_id
= (SELECT city_id
FROM Cities AS C1
WHERE C1.city = Streets.city);
Neat, clean, logical, portable code.
--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!
Grant Case
I will have to disagree partially with your last statement...portable,
yes...neat, logical, and clean, no. The problem as I see it comes when you
have to do multiple field updates based upon criteria from another table.
For instance, the following is taken from production problem we had today
with our system of record. Intermittenly, our system of record will send a
transaction to our GLWORK table without a GL table or an identifier for the
CostCenter (this is the company and branch fields).
So an abbreviated DDL (the actual vendor tables have many more fields):
CREATE TABLE Property
(
Pnoteno VARCHAR (14) NOT NULL,
Punit CHAR (3) NOT NULL,
PGLTable CHAR (3) NOT NULL,
PCompany CHAR (6) NOT NULL,
PBranch CHAR (6) NOT NULL
)
CREATE TABLE GLWork
(
GLNote VARCHAR (14) NOT NULL,
GLUnit CHAR (3) NOT NULL,
GLTable CHAR (3) NOT NULL DEFAULT 'TCL',
GLCompany CHAR (6) NOT NULL DEFAULT '',
GLBranch CHAR (6) NOT NULL DEFAULT '',
GLAmount FLOAT NOT NULL DEFAULT 0
)
CREATE TABLE Company
(
Company CHAR (6) NOT NULL,
Branch CHAR (6) NOT NULL,
CostCenter VARCHAR (6) NOT NULL
)
We have exception reports built for this event so this is what triggers
someone to go update the GLWORK table. In order for this transaction
created in GLWORK to report correctly, the GLTable, GLCompany, and GLBranch
fields must be populated. So the query in T-SQL goes something like this:
UPDATE GLWork
SET GLCompany = PCompany,
GLBranch = PBranch,
GLTable = PGLTable
FROM GLWork
INNER JOIN Property ON Pnoteno = GLNote AND PUnit = GLUnit
WHERE GLNote = @NoteNumber AND GLUnit = @UnitNumber
Pretty simple query in T-SQL. Let's look at the same query in standard ANSI
SQL and compare them:
UPDATE GLWork
SET GLCompany = (SELECT PCompany FROM Property WHERE PNoteno = @NoteNumber
AND Punit = @UnitNumber),
GLBranch = (SELECT PBranch FROM Property WHERE PNoteno = @NoteNumber AND
Punit = @UnitNumber),
GLTable = (SELECT PGLTable FROM Property WHERE PNoteno = @NoteNumber AND
Punit = @UnitNumber)
WHERE GLNote = @NoteNumber AND GLUnit = @UnitNumber
I had to write three different queries referencing the same table, in order
to complete the same work in T-SQL. Also, I have had to include eight
variable references in the ANSI query instead of two because I only want to
return the information for one loan instead of every loan in Property thus
the WHERE clause @NoteNumber and @UnitNumber in the SET operation. Now
imagine having to update 30 to 40 fields on a table based upon this
information. Personally, that doesn't thrill me too much and neither does
breaking the code out into 40 different updates especially given the easy
possibility of including the wrong field in the SET SELECT operation. While
it may be ANSI SQL standard, I believe it to be too much work for not enough
benefit. Knowing your position within the SQL communitity Joe, I would hope
you might suggest this syntax as possible standard. You can say one good
thing about Microsoft in general. Microsoft tries to eliminate impediments
to developer productivity and this to me is one of those roadblocks they
knocked down. I will keep it in mind if I ever have to port to Oracle or
another DBMS, but for now I do enjoy the simplicity, logical, and clean
syntax for the UPDATE FROM clause in T-SQL.
We can argue semantics and reasons for not using proprietary syntax but this
is not like ANSI JOINS where it just makes sense to keep the joins out of
the WHERE clause. I think in readability and developer productivity ANSI
JOINS make sense because its more legible, easier to determine table
relationships, and less prone to error. As for ANSI UPDATEs, as shown in
the code above. If I knew nothing of the code and nothing of SQL it would
be easier to understand the T-SQL syntax then the ANSI. In addition, its
the difference between 351 characters to 196 to accomplish the exact same
task. I am a supporter of standards, but this not a place where I can hold
up the SQL standard and say that this is much better to keep with the
standard. Hopefully, the SQL standard will change to include the syntax
above as standard because it is neater, cleaner and more logical.
Grant
"Joe Celko" <joe....@northface.edu> wrote in message
news:emgPMVQw...@TK2MSFTNGP09.phx.gbl...
Tibor Karaszi
I doubt Joe will endorse the SQL Server UPDATE with join syntax considering
the things he says about it, but I let him speak for himself :-).
In ANSI SQL, using a "row value constructor", you can do:
UPDATE Streets
SET (streets.city_id, streets.zip)
= (SELECT city_id, zip
FROM Cities AS C1
WHERE C1.city = Streets.city);
But unfortunately SQL Server doesn't support row value constructors.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Grant Case" <hangt...@DONThSoPtAmMail.com> wrote in message
news:%235ZbJ$RwDHA...@TK2MSFTNGP09.phx.gbl...
Rufus T. Firefly
and portability (to/from S2k) in the same breath?
MS has had years to implement sql99 features but
hasn't.Their major competitors have it.I'm not
clear just what Yukon will even have in the way of sql99.
For MS I can understand business is business.
What about you?:~)
Joe Celko
the same breath? <<
The federal goverment politely refers to SQL-99 as "an emerging
standard" then requires SQL-92 instead. There is no FIPS-127 test for
SQL-99, while there is such a suite for SQL-92. The whole FIPS scandal
is another case of "FOB" (Friends of Bill) getting a few bucks at the
expense of the people, but Mike Gorman has covered some of the details
in his writings.
>> MS has had years to implement sql99 features but
hasn't. <<
They are not alone. Frankly, the big boys are still trying to get full
SQL-92. SQL-99 is at best a place to look when you are shoppping for a
new feature or for new syntax for an existing feature. Actually
implementing it would be a nightmare because of arrays, nested tables,
order by clauses in queries, etc.
>> I'm not clear just what Yukon will even have in the way of sql99. <<
Me neither; I just want to know how much SQL-92 we are going to get.
Will there be row constructors? CREATE DOMAIN? CREATE ASSERTION?
OVERLAPS()? optional standard syntax for existing features, like
CURRENT_TIMESTAMP for getdate()?
CLR scares me because I see kids who know a CLR language using SQL
Server as a file system in their native language and never learning
proper SQL.
Joe Celko
Columns are nor fields' rows are not records. You have the wrong
mindset and are still thinking in terms of sequential processing. The
SQL-99 syntax for this kind of thing is:
UPDATE <table>
SET ROW = <row expression>
[WHERE <serch condition>];
The keyword ROW is a shorthand for a list of the columns in the <table>.
If some of the columns a have defaults, you can use a row constructor
like you have in a VALUES() clause of an INSERT INTO statement.
Steve Kass
Joe Celko wrote:
>>>why don't you ever talk about SQL-99 and portability (to/from S2k) in
>>>
>>>
>the same breath? <<
>
>The federal goverment politely refers to SQL-99 as "an emerging
>standard" then requires SQL-92 instead. There is no FIPS-127 test for
>SQL-99, while there is such a suite for SQL-92. The whole FIPS scandal
>is another case of "FOB" (Friends of Bill) getting a few bucks at the
>expense of the people, but Mike Gorman has covered some of the details
>in his writings.
>
>
>
>>>MS has had years to implement sql99 features but
>>>
>>>
>hasn't. <<
>
>They are not alone. Frankly, the big boys are still trying to get full
>SQL-92. SQL-99 is at best a place to look when you are shoppping for a
>new feature or for new syntax for an existing feature. Actually
>implementing it would be a nightmare because of arrays, nested tables,
>order by clauses in queries, etc.
>
>
>
>>>I'm not clear just what Yukon will even have in the way of sql99. <<
>>>
>>>
>
>Me neither; I just want to know how much SQL-92 we are going to get.
>Will there be row constructors? CREATE DOMAIN? CREATE ASSERTION?
>OVERLAPS()? optional standard syntax for existing features, like
>CURRENT_TIMESTAMP for getdate()?
>
>
select current_timestamp is valid syntax in SQL Server 2000
SK