January 14, 2016 at 5:44 pm
Hi, I've read that if a CTE is based on two or more tables and if the update statement to update this CTE affects only one of the base tables, then the update will be successful. However, the results of the update will not always be what one expects.
Is there a way to know if the update of a CTE, which is based on multiple tables, will not only be successful but the results will also be as we expect? If not, should such a CTE be used to update a table at all? Thanks in advance.
January 15, 2016 at 2:34 am
The Kamagra helps men with erectile Cracked http://www.kamagra-sure.com/ by modifying the erectile response when a man greatly strengthened.
January 15, 2016 at 2:50 am
Sounds like an extension of the UPDATE...FROM issue, where you can get unreported cardinality errors. If you update TableA based on a join to TableB, and there are multiple rows in TableB for each row in TableB, SQL Server will choose the value from one of the rows in TableB and use that to update TableB, without reporting an error. You can avoid this by considering which direction your many-to-one relationship goes in before writing the query, or you can write your query in a different way.
Here are examples of the above - they're not tested, so please forgive any syntax errors.
-- This can cause unreported cardinality errors
UPDATE a
SET MyCol = b.MyCol
FROM TableA a
JOIN TableB b
ON a.ID = b.ID
-- This will return an error if there is more than one value for a.MyCol
UPDATE TableA a
SET a.MyCol = (
SELECT b.MyCol
FROM TableB b
WHERE b.ID = a.ID
)
John
January 15, 2016 at 5:27 am
Is there a way to know if the update of a CTE, which is based on multiple tables, will not only be successful but the results will also be as we expect? If not, should such a CTE be used to update a table at all? Thanks in advance.
I am not sure about your question, but even multiple join can be easily updated via CTE
I have quickly created a small query for you:
create table demo1(id int,Name varchar(10),Roll1 int)
create table demo2(id int ,Name varchar(10),Roll2 int)
insert into demo1(id,Name,Roll1) values(1,'a1',1)
insert into demo2(id,Name,Roll2) values(1,'a2',2)
select * from demo1
select * from demo2
select * From demo1
select * from demo2
;with cte
as (
select demo1.id As Id1,demo1.Name as Name1,demo2.Name as Name2
from demo1
JOIN demo2 on demo1.id = demo2.id
)
--select * from cte
update cte set ID1 = 3 where Name1 ='a1'
select * From demo1
select * from demo2
From above query, sql exactly did what we expected.
We expected the change in demo1 table and it did.
So you have to be sure what you want to update.
Read some blogs for more information on CTE which are just like inline views
January 15, 2016 at 9:41 am
John Mitchell-245523 (1/15/2016)
You can avoid this by considering which direction your many-to-one relationship goes in before writing the query, or you can write your query in a different way.
The MERGE statement insulates us from the problem of an UPDATE-JOIN having a poorly formed JOIN and updating the same row multiple times. For this reason during code reviews I will often suggest using MERGE in place of an UPDATE-JOIN. Although this is not much value to some, as an aside UPDATE-JOIN is a proprietary syntax whereas MERGE is an ANSI-supported construct. Here is the error we see from MERGE when we try updating the same row multiple times whereas with UPDATE-JOIN the query happily completes and the results are potentially unpredictable as it depends on which source row was used to update the target row last as to what result we might get and that could differ from execution to execution:
[font="Courier New"]Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.[/font]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply