This is not supported in MSsql.
update T1 set (theUpdatedValu e, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)
Is there a workaround?
Other than doing it in a loop from eg asp, over either fields (one statement
per field), or over records (a query with a loop which for each row does a
select from one table, update other table with the selected values.) 3 17977
This is not supported in MSsql.
>
update T1 set (theUpdatedValu e, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)
Is there a workaround?
You can use the proprietary UPDATE...FROM syntax:
UPDATE T1
SET
theUpdatedValue = T2.theTop,
theOtherValue = T2.theValue
FROM T2
WHERE
T2.theKey = T1.theID
Or with an alias:
UPDATE a
SET
theUpdatedValue = b.theTop,
theOtherValue = b.theValue
FROM T1 a
JOIN T2 b ON
b.theKey = a.theID
--
Hope this helps.
Dan Guzman
SQL Server MVP http://weblogs.sqlteam.com/dang/
"Leif Neland" <le**@neland.dk wrote in message
news:48******** *************** @dtext02.news.t ele.dk...
This is not supported in MSsql.
update T1 set (theUpdatedValu e, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)
Is there a workaround?
Other than doing it in a loop from eg asp, over either fields (one
statement
per field), or over records (a query with a loop which for each row does a
select from one table, update other table with the selected values.)
On SQL Server 2005 you can use CTE to perform the update:
WITH UpdateSet
AS
(SELECT theUpdatedValue , theOtherValue, theTop, theValue
FROM T1
JOIN T2
ON T1.theID = T2.theKey)
UPDATE UpdateSet
SET theUpdatedValue = theTop,
theOtherValue = theValue;
HTH,
Plamen Ratchev http://www.SQLStudio.com
The proprietary syntax does not work all the time:
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);
INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);
CREATE TABLE OrderDetails
(order_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL,
item_price DECIMAL (9,2) NOT NULL,
PRIMARY KEY(order_nbr, sku),
-- FOREIGN KEY(sku) REFERENCES Products(sku)
FOREIGN KEY(order_nbr) REFERENCES Orders(order_nb r));
INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);
SELECT * FROM Orders;
UPDATE Orders
SET Orders.some_col = OrderDetails.it em_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nb r = OrderDetails.or der_nbr;
results -- see item #1; last physical value
1 205.00 - where is the $500.00?
2 490.95
3 480.00
--repeat with new physical ordering
DELETE FROM OrderDetails;
DELETE FROM Orders;
DROP INDEX OrderDetails.fo obar;
-- index will change the execution plan
CREATE INDEX foobar ON OrderDetails (order_nbr, item_price);
INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);
UPDATE Orders
SET Orders.some_col = OrderDetails.it em_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nb r = OrderDetails.or der_nbr;
SELECT * FROM Orders;
Results
1 500.00
2 490.95
3 480.00
What is the first property that you must have in an INDEX? It cannot
change the results of a statement, only the performance. See the
problem?
This would not have happened with the ANSI syntax. That's the point
that I am trying to make. The ANSI equivalent of the incorrect query
above is
UPDATE Orders -- no alias allowed!
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.or der_nbr = Orders.order_nb r)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.or der_nbr = Orders.order_nb r);
This will of course result in an error, and even the most junior of
junior programmers will eventually figure out (probably by asking a
senior) what's wrong. At that point, either the query is corrected to
match the request, or a note is sent back to management asking for a
clarification of the ambiguity in the request.
Bottom line: UPDATE FROM *can* be safely used - but only if you are
FOREVER certain that no single row in the target table can EVER be
joined to more than one row in the source table(s); FOREVER in the
ENTIRE LIFETIME of the application; FOREVER across all programmers yet
to come.
I like to err on the safe side, I do not bet only an endless stream of
100% perfect programmers. You correctly guessed the row constructor
syntax that is standard in ANSI/ISO SQL, by the way. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: allenj |
last post by:
DB2 UDB 7.2 WSE
Fixpak 9
Linux Red Hat 7.3
I have some library code (written in Java, if that matters) that
processes maintenance screens that are presented to the end-users
as forms in a browser.
Because the code is generic, working against any table, I am
dynamically generating UPDATE statements that update
|
by: Todd |
last post by:
Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in a
condition, otherwise I need to use a tstamp from TableA (note:there
are additional tables and conditions for this query, but this problem
is based around these 2). I attempted having TableB (as B) "left
outer joined" to TableA, and a condition...
|
by: James |
last post by:
I have a performance problem with the following query and variations on the
subselect. The EXISTS version of the first example will complete in ~10
minutes. The NOT logic in both the examples makes them both keep running
long enough that a communications error is the only result returned so far.
This is a federated view computer ~160 k rows, computer_sys_id is PK .
matched_sware ~ 18 million no PK , no index.
Any suggestions on...
|
by: David Mitchell |
last post by:
Apologies for the Subject Heading!
I am working on a small invoicing app for a local Tyre and Exhaust
Centre. They want to be able to type in the Vehicle Registration
Number and it automatically bring up the customers name and address.
This in itself seems fairly straightforward except that 1 customer can
have many vehicles.
My tables are set up as follows:
|
by: Tc |
last post by:
Hi,
I was curious, I am thinking of writing an application that loads a dataset
from a database that resides on a server. The question I have is this, if
multiple copies of the app will be running at once will there be problems
with data updates?
The reason I ask is I'm thinking like this:
User1 launches the app and the dataset is created from the data in the DB.
| |
by: cwbp17 |
last post by:
I'm having trouble updating individual datagrid cells.
Have two tables car_master (columns include Car_ID, YEAR,VEHICLE) and
car_detail (columns include Car_ID,PRICE,MILEAGE,and BODY);both tables have a
FK relationship on CAR_ID
so the oracledataadapter1 select statement(CommandText) is:
select car_master.car_id,
car_master.year,car_master.vehicle,car_detail.car_id AS EXPR1,
|
by: k4 |
last post by:
I have a database that contains a column for UnitName , BeginDate and
EndDate.
I want to pass two parameters (@BeginDate and @EndDate) and retrieve a
table of values
that include UnitName along with Counts for each UnitName.
SELECT UnitName, COUNT(BeginDate) AS Start
(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND
|
by: bill |
last post by:
In an application I am writing the user can define a series of
steps to be followed. I save them in a sql database using the
field "order" (a smallint) as the primary key.
(there are in the range of 20 steps)
On the admin page the steps are listed, in "order" order and the
user can create new steps and assign an order and all is well.
The problem may come in using a renumber function which should
take the steps in their current order...
|
by: CodeButcher |
last post by:
I have a subselect in my select statement:
select t1.a, t1.b, (select top 1 t2.a from where)
what I need is:
select t1.a, t1.b, (select top 1 t2.a, t2.b from where)
However, I get the error that I can only have one field.
Here is the sql. I'm stumped. Thanks for your help...
|
by: Mike |
last post by:
So here's the situation (.NET 2.0 btw):
I have a form, and on this form is a textbox among many other
databound controls. The textbox is bound to a field in a data table
via the Text property. In this table there are multiple columns that
cannot be NULL, which, are bound to other controls (but they're not
really important at this time).
I create a new row via the currency manager like so:
_currencyManager.AddNew()
_currentRow =...
|
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: 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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: 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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
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.
| |