In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.Uniq ueID =
tblMyTableTEMP. UniqueID
SET
tblMyTable.myFi eld = tblMyTableTEMP. myField,
tblMyTable.myFi eld2 = tblMyTableTEMP. myField2,
tblMyTable.myFi eld3 = tblMyTableTEMP. myField3
How is this done in a SQL Server Stored Procedure?
Any help is appreciated.
lq 8 89314
Lauren,
That's an interesting update. I've never done an update with a JOIN in
it before and, out of curiosity, I tried to make it work, but was
unsuccessful.
The way I would normally do this in an SP would be like this:
create procedure update_mytable as
begin
UPDATE tblMyTable
set myField = (select t.myField from tblMyTableTemp t where t.UniqueID =
tblMyTable.Uniq ueID),
myField2 = (select t.myField2 from tblMyTableTemp t where t.UniqueID =
tblMyTable.Uniq ueID),
myField3 = (select t.myField3 from tblMyTableTemp t where t.UniqueID =
tblMyTable.Uniq ueID)
end
Since my solution requires 3 trips to tblMyTableTemp, if anyone knows how to
do this update in a similar fashion to the way Lauren did it below, pls
advise. I'd love to add that to my "bag o' tricks".
Thanks and Hope that helps.
Chuck Conover www.TechnicalVideos.net
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com... In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID:
UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.Uniq ueID = tblMyTableTEMP. UniqueID SET tblMyTable.myFi eld = tblMyTableTEMP. myField, tblMyTable.myFi eld2 = tblMyTableTEMP. myField2, tblMyTable.myFi eld3 = tblMyTableTEMP. myField3
How is this done in a SQL Server Stored Procedure? Any help is appreciated. lq
Lauren Quantrell (la************ *@hotmail.com) writes: In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID:
UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.Uniq ueID = tblMyTableTEMP. UniqueID SET tblMyTable.myFi eld = tblMyTableTEMP. myField, tblMyTable.myFi eld2 = tblMyTableTEMP. myField2, tblMyTable.myFi eld3 = tblMyTableTEMP. myField3
How is this done in a SQL Server Stored Procedure? Any help is appreciated.
UPDATE tblMyTable
SET myField = b.myField,
myField2 = b.myField2,
myField3 = b.myField3
FROM tblmyTable a
RIGHT JOIN tblMyTableTEMP b ON a.UniqueID = b.UniqueID
Here I'm using aliases, but this is not necessary. But I prefer using
aliases, as this reduces the amount of noice in the query coming from
the repeated table names.
But this query looks a bit funny. A LEFT JOIN would make sense. The
you would set the columns to NULL, if there is no match in
tblMyTableTEMP. But a RIGHT JOIN? I cannot see that you get the same
result from an inner join.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
>> In VBA, I constructed the following to update all records in
MyTable with each records [sic] in MyTableTemp HAVING the same
uniqueid: <<
Read ISO-11179 so you'll stop putting "tbl-" in front of data element
names; yes, I know this sample code, but it really looks awful. Also,
I hope you know that a row is NOT a record, a column is NOT a field
and a table is NOT a file.
UPDATE
MyTable
RIGHT OUTER JOIN
MyTableTemp
ON MyTable.uniquei d = MyTableTemp.uni queid
SET MyTable.myfield = MyTableTemp.myf ield,
MyTable.myfield 2 = MyTableTemp.myf ield2,
MyTable.myfield 3 = MyTableTemp.myf ield3;
This makes no sense in Standard SQL and does not port to other
platforms. 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 ALL predicate? CURRENT_TIMESTA MP?
Etc.?
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 or table expression 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 has been created in the database. That table then
is dropped at the end of the statement. If you allowed correlation
names or table expression 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.
In SQL-92, you could use a row constructor:
UPDATE MyTable
SET ROW (myfield, myfield2, myfield3)
= (SELECT myfield, myfield2, myfield3
FROM MyTableTemp
WHERE MyTable.uniquei d = MyTableTemp.uni queid);
But in SQL Server, you have to use
UPDATE MyTable
SET myfield
= (SELECT myfield, myfield2, myfield3
FROM MyTableTemp
WHERE MyTable.uniquei d = MyTableTemp.uni queid),
myfield2
= (SELECT myfield2
FROM MyTableTemp
WHERE MyTable.uniquei d = MyTableTemp.uni queid),
myfield3
= (SELECT myfield3
FROM MyTableTemp
WHERE MyTable.uniquei d = MyTableTemp.uni queid);
Watch out for the fact that an empty scalar query will become a NULL.
Chuck,
I did find a solution for your bag of tricks:
Alter Procedure "mySPName"
As
set nocount on
UPDATE
tblName
SET
tblName.FieldOn e = tblNameTEMP.Fie ldOne,
tblName.FieldTw o = tblNameTEMP.Fie ldTwo,
tblName.FieldTh ree = tblNameTEMP.Fie ldThree,
FROM
tblNameTEMP
WHERE
tblName.UniqueI D = tblNameTEMP.Uni queID
lq
"Chuck Conover" <cc******@comms peed.net> wrote in message news:<10******* ********@news.c ommspeed.net>.. . Lauren, That's an interesting update. I've never done an update with a JOIN in it before and, out of curiosity, I tried to make it work, but was unsuccessful. The way I would normally do this in an SP would be like this:
create procedure update_mytable as begin UPDATE tblMyTable set myField = (select t.myField from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID), myField2 = (select t.myField2 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID), myField3 = (select t.myField3 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID) end
Since my solution requires 3 trips to tblMyTableTemp, if anyone knows how to do this update in a similar fashion to the way Lauren did it below, pls advise. I'd love to add that to my "bag o' tricks".
Thanks and Hope that helps. Chuck Conover www.TechnicalVideos.net
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message news:47******** *************** ***@posting.goo gle.com... In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID:
UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.Uniq ueID = tblMyTableTEMP. UniqueID SET tblMyTable.myFi eld = tblMyTableTEMP. myField, tblMyTable.myFi eld2 = tblMyTableTEMP. myField2, tblMyTable.myFi eld3 = tblMyTableTEMP. myField3
How is this done in a SQL Server Stored Procedure? Any help is appreciated. lq
Erland,
This is what I ended up with:
Alter Procedure "mySPName"
As
set nocount on
UPDATE
tblName
SET
tblName.FieldOn e = tblNameTEMP.Fie ldOne,
tblName.FieldTw o = tblNameTEMP.Fie ldTwo,
tblName.FieldTh ree = tblNameTEMP.Fie ldThree,
FROM
tblNameTEMP
WHERE
tblName.UniqueI D = tblNameTEMP.Uni queID
lq
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************@1 27.0.0.1>... Lauren Quantrell (la************ *@hotmail.com) writes: In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID:
UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.Uniq ueID = tblMyTableTEMP. UniqueID SET tblMyTable.myFi eld = tblMyTableTEMP. myField, tblMyTable.myFi eld2 = tblMyTableTEMP. myField2, tblMyTable.myFi eld3 = tblMyTableTEMP. myField3
How is this done in a SQL Server Stored Procedure? Any help is appreciated.
UPDATE tblMyTable SET myField = b.myField, myField2 = b.myField2, myField3 = b.myField3 FROM tblmyTable a RIGHT JOIN tblMyTableTEMP b ON a.UniqueID = b.UniqueID
Here I'm using aliases, but this is not necessary. But I prefer using aliases, as this reduces the amount of noice in the query coming from the repeated table names.
But this query looks a bit funny. A LEFT JOIN would make sense. The you would set the columns to NULL, if there is no match in tblMyTableTEMP. But a RIGHT JOIN? I cannot see that you get the same result from an inner join.
> UPDATE tblMyTable set myField = (select t.myField from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID), myField2 = (select t.myField2 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID), myField3 = (select t.myField3 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID)
Since my solution requires 3 trips to tblMyTableTemp
Doesn't SQL Server support...
UPDATE tblMyTable
set (myField, myField2, myFeld3) = (select t.myField, t.myField2, t.myField3
from tblMyTableTemp t
where t.UniqueID = tblMyTable.Uniq ueID)
where exists (select *
from tblMyTableTemp t1
where t1.UniqueID = tblMyTable.Uniq ueID);
Christian.
Christian,
No, that one doesn't work.
Regards,
Chuck Conover www.TechnicalVideos.net
"Christian Maslen" <ch************ **@techie.com> wrote in message
news:b9******** *************** ***@posting.goo gle.com... UPDATE tblMyTable set myField = (select t.myField from tblMyTableTemp t where t.UniqueID
= tblMyTable.Uniq ueID), myField2 = (select t.myField2 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID), myField3 = (select t.myField3 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID) Since my solution requires 3 trips to tblMyTableTemp
Doesn't SQL Server support...
UPDATE tblMyTable set (myField, myField2, myFeld3) = (select t.myField, t.myField2,
t.myField3 from tblMyTableTemp t where t.UniqueID =
tblMyTable.Uniq ueID) where exists (select * from tblMyTableTemp t1 where t1.UniqueID = tblMyTable.Uniq ueID);
Christian.
Lauren,
Thanks. I tried that and it works great. It also only accesses each
table once, so it's a good solution.
Thanks and Best Regards,
Chuck Conover www.TechnicalVideos.net
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com... Chuck, I did find a solution for your bag of tricks:
Alter Procedure "mySPName" As set nocount on UPDATE tblName SET tblName.FieldOn e = tblNameTEMP.Fie ldOne, tblName.FieldTw o = tblNameTEMP.Fie ldTwo, tblName.FieldTh ree = tblNameTEMP.Fie ldThree, FROM tblNameTEMP WHERE tblName.UniqueI D = tblNameTEMP.Uni queID
lq "Chuck Conover" <cc******@comms peed.net> wrote in message
news:<10******* ********@news.c ommspeed.net>.. . Lauren, That's an interesting update. I've never done an update with a JOIN
in it before and, out of curiosity, I tried to make it work, but was unsuccessful. The way I would normally do this in an SP would be like this:
create procedure update_mytable as begin UPDATE tblMyTable set myField = (select t.myField from tblMyTableTemp t where t.UniqueID
= tblMyTable.Uniq ueID), myField2 = (select t.myField2 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID), myField3 = (select t.myField3 from tblMyTableTemp t where t.UniqueID = tblMyTable.Uniq ueID) end
Since my solution requires 3 trips to tblMyTableTemp, if anyone knows
how to do this update in a similar fashion to the way Lauren did it below, pls advise. I'd love to add that to my "bag o' tricks".
Thanks and Hope that helps. Chuck Conover www.TechnicalVideos.net
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message news:47******** *************** ***@posting.goo gle.com... In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID:
UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.Uniq ueID = tblMyTableTEMP. UniqueID SET tblMyTable.myFi eld = tblMyTableTEMP. myField, tblMyTable.myFi eld2 = tblMyTableTEMP. myField2, tblMyTable.myFi eld3 = tblMyTableTEMP. myField3
How is this done in a SQL Server Stored Procedure? Any help is appreciated. lq This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Karaoke Prince |
last post by:
Hi There,
I have an update statement to update a field of a table (~15,000,000
records). It took me around 3 hours to finish 2 weeks ago. After that
no one touched the server and no configuration changed. Until
yesterday, I re-ran it again and it took me more than 18hrs and still
not yet finished!!!
What's wrong with it? I can ran it successfully before. I have tried
two times but the result was still the same.
|
by: Dominic |
last post by:
In our SQL database server, I set up a linked server
. In this example, the linked server simply points
back to the server.
I did the following query at the SQL query analyser, and it worked
fine.
select * from .test_database.dbo.test_table
where test_col = 'def'
|
by: Felix |
last post by:
Dear Sql Server experts:
First off, I am no sql server expert :)
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I'd gleaned from
reading newsgroups. Nothing worked....
|
by: Dan Berlin |
last post by:
I have two tables:
T1 : Key as bigint, Data as char(20) - size: 61M records
T2 : Key as bigint, Data as char(20) - size: 5M records
T2 is the smaller, with 5 million records.
They both have clustered indexes on Key.
I want to do:
|
by: sqlgoogle |
last post by:
Hi I'm having update problem.
Here is the senario
I have to different db server (SQL Server) linked with each other
In DB Server 1 I have 2 tables &
In DB Server 2 I have 3 tables.
I have joined tables with each other first & then between the servers
When I run select on DB Server1 with both tables joined I'm getting
more values then when I run the select between the DB Servers (about 20
| |
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
|
by: Metal2You |
last post by:
I'm working on an ASP.NET 2.0 application in Visual Studio 2005 that
accesses a Sybase database back end. We're using Sybase SQL Anywhere
9.0.2.3228. I have installed and registered the Sybase .NET 2.0
DataProvider (iAnywhere.Data.AsaClient.dll) into the GAC so it can be
used in the ProviderName property of a SQLDataSource and loads properly
at run time.
The application I'm writing is a bit more complex than the example I'm
about to...
|
by: devine |
last post by:
Hi All,
I am trying to send an automatic email when an update has been made.
My update statement will updates 6 fields, and dependant on one of the fields, I would like to send an email using CDO.
Once the update is made, I am trying to re-query the database to retrieve all the fields that need to be included in the email, but it's just not working for me!!
This my code to update
<%
Set Conn = Server.CreateObject("ADODB.Connection")...
|
by: Andi Clemens |
last post by:
Hi,
I want to update our DNS servers periodically with some IP addresses. But I
don't know how to do this.
I searched the Internet quite a while but I haven't found a good example how
to do this.
I want to change the A-Record for some IPs, this shouldn't be too hard.
I looked at dnspython and twisted, but I really don't have a clue how to do
this.
Has anyone done this before?
|
by: Richard |
last post by:
Hello!
I have this piece of SQL code:
UPDATE a
SET Field1 = c.Field1
FROM a
INNER JOIN b ON a.GUID1 = b.GUID1
INNER JOIN c ON b.GUID2 = c.GUID2
WHERE c.Type = 1
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |