September 29, 2015 at 8:32 am
Can you update data from multiple tables in the same UPDATE statement, by joining those tables in a CTE ?
For example, this fails:
DECLARE @UPDCATE_COUNT AS int = 100000;
WITH COMBINED_TABLES AS (
SELECT TOP (@UPDATE_COUNT) T.UpdateID, T.IS_UPDATED, U.[Description]
FROM dbo.Table1 AS U
INNER JOIN dbo.Table2 AS T
ON U.UpdateID = T.UpdateID
AND T.IS_UPDATED = 0
ORDER BY T.UpdateID
)
UPDATE C
SET C.IS_UPDATED = 1
, C.[Description] = CAST([Description] AS nvarchar(max))
FROM COMBINED_TABLES AS C;
I get this error:
Msg 4405, Level 16, State 1, Line 29
View or function 'C' is not updatable because the modification affects multiple base tables.
Is there a way to do this in a single update?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 29, 2015 at 9:03 am
Can you update data from multiple tables in the same UPDATE statement, by joining those tables in a CTE ?
No, you can't.
From BOL
Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
- Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
- The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
* An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
* A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
- The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
- TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. For more information, see Modify Data Through a View.
If the previous restrictions prevent you from modifying data directly through a view, consider the following options:
INSTEAD OF Triggers
INSTEAD OF triggers can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. For more information about INSTEAD OF triggers, see DML Triggers.
September 29, 2015 at 9:05 am
Yeah, the moment I got the error I was afraid of that. Darned inconvenient. I'll use the OUTPUT clause instead and do a second update. Thanks!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 29, 2015 at 9:14 am
Since CTEs are essentially single-use views, they follow the same rules as updatable views; specifically
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 29, 2015 at 11:04 am
You CAN, however, build an update VIEW that includes an INSTEAD OF TRIGGER, allowing you to update more than one TABLE from the CTE through the VIEW, based on more sophisticated logic.
More info: Designing INSTEAD OF Triggers
September 29, 2015 at 11:21 am
Graeme Martin (9/29/2015)
You CAN, however, build an update VIEW that includes an INSTEAD OF TRIGGER, allowing you to update more than one TABLE from the CTE through the VIEW, based on more sophisticated logic.More info: Designing INSTEAD OF Triggers
For what I need to do, that's overkill, and probably wouldn't be practical in the environment I'm working with. I'll just use the OUTPUT clause to populate a temp table with a primary key value, and do the other UPDATE separately.
Now, however, I need a way to find the data type of nvarchar(max) or varchar(max) from whatever combination of system views or tables can provide that for a given table name and column name. I can't seem to find a way to determine the actual length of a varchar or nvarchar field that was declared at table creation time. Anyone?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 29, 2015 at 11:26 am
Do you mean something like this?
CREATE TABLE CharsTest(
myvarchar varchar(10),
myNvarchar nvarchar(10),
myLvarchar varchar(max));
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CharsTest';
DROP TABLE CharsTest;
September 29, 2015 at 12:46 pm
Luis Cazares (9/29/2015)
Do you mean something like this?
CREATE TABLE CharsTest(
myvarchar varchar(10),
myNvarchar nvarchar(10),
myLvarchar varchar(max));
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CharsTest';
DROP TABLE CharsTest;
What it turned out I needed was the following:
SELECT CO.DATA_TYPE + '(' +
CASE CO.CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'max'
ELSE CAST(CO.CHARACTER_MAXIMUM_LENGTH AS varchar(4))
END + ')'
FROM INFORMATION_SCHEMA.COLUMNS AS CO;
I was just not finding any one field that had the datatype exactly as it appears when you look at the table columns in SSMS.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply