July 16, 2015 at 12:18 am
There seems to be an issue with CTE update or am I making any mistake? Can you please look into this.
SELECT [id]
,[name]
FROM [dbo].[test]
idname
1 d
1 e
SELECT [ID]
,[Name]
,[Address]
FROM [dbo].
IDNameAddress
1a x
2b y
3d z
Now I expect the below statement to fail :
with cte as ( select * from [dbo].[test])
update dbo.s
set s.name = cte.name
from cte
where s.id = cte.id
But its updating id 1 with d.
SELECT [ID]
,[Name]
,[Address]
FROM [dbo].
IDNameAddress
1d x
2by
3dz
Even this direct update also working
update dbo.s
set s.name = dbo.test.name
from dbo.test
where s.id = test.id
I forgot my SQL 🙁
July 16, 2015 at 12:48 am
I donot think you are loosing any thing. It a simple understanding issue. for the sake of understanding i have created an example below to give you an idea about it.
USE tempdb
GO
Create table SampleTable
(
ID int,
Code varchar(20)
)
Create table test
(
ID int,
Code varchar(20)
)
insert into SampleTable
select 1, 'Code 1' union all
select 2, 'Code 2' union all
select 3, 'Code 3' union all
select 4, 'Code 4' union all
select 5, 'Code 3' union all
select 6, 'Code 4'
insert into test
select 1, 'Code 1' union all
select 1, 'Code 2'
-------- before update
select * from SampleTable
------------- This update will not give any error.
update s
set s.Code = t.Code
from SampleTable s
join test t on s.id = t.id
-------- after update result
select * from SampleTable
/***************************
Merge Statement
***************************/
Merge SampleTable as s
Using test as t
ON s.id = t.id
WHEN MATCHED THEN
UPDATE SET S.CODE = T.CODE
/*********** This merge statement will give you following error
Msg 8672, Level 16, State 1, Line 44
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.
***********************/
;
----- clean up
DROP TABLE SampleTable
DROP TABLE TEST
if you still have any queries do let us know. Hope it helps.
July 16, 2015 at 12:55 am
Thanks for your reply mate.
Exactly this is what my problem.
When the source has duplicate ids, how its updating destination.
How will it know which record to take and update. In this case how the sql is updating code1 why not code 2 where id =1 in both cases
July 16, 2015 at 1:37 am
Well to understand this you will have to look at the actual execution plan for this update statement.
There you will find the Stream Aggregate operator in the plan. for detail understanding check this Showplan Operator of the Week - Stream Aggregate[/url]
For Update statement following is the detail planned.
-------- after update result
|--Table Update(OBJECT:([tempdb].[dbo].[SampleTable] AS ), SET:([tempdb].[dbo].[SampleTable].[Code] as .[Code] = [tempdb].[dbo].[test].[Code] as [t].[Code]))
|--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([t].[Code]=ANY([tempdb].[dbo].[test].[Code] as [t].[Code])))
|--Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[test].[ID] as [t].[ID]=[tempdb].[dbo].[SampleTable].[ID] as .[ID]))
|--Table Scan(OBJECT:([tempdb].[dbo].[SampleTable] AS ))
|--Table Scan(OBJECT:([tempdb].[dbo].[test] AS [t]))
The statement you should be most interested in is
|--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([t].[Code]=ANY([tempdb].[dbo].[test].[Code] as [t].[Code])))
if you look closely you will find ANY()
ANY operator returns true when the scalar value matches with any value in the single-column set of values. ALL operator returns true when the scalar value matches with all value in the single-column set of values. The comparison can be extended with <, >, and <> operators too.
for more detail you can check this link
and some very good understanding have been shared by Undocumented Query Plans: The ANY Aggregate by Paul White.
hope it helps.
July 16, 2015 at 4:49 pm
Wonderful..
So its using ANY Operator due to which its getting updated with any record.
What should I do get it updated with a specific record. When there are duplicates we never know which one it should take.
Especially in a stored procedure or function there will be set of instructions , I expect it to fail when there are duplicates.
How can I do this ?
In the above example , how can i force it to update with code2 instead of code1.
July 16, 2015 at 5:49 pm
A common practice is to use ROW_NUMBER() in a CTE to set the priority and then select the record in the main query where the row number = 1. This requires well defined criteria to set up the correct PARTITION BY and ORDER BY clauses. You haven't provided enough details to determine what--if any--those well defined criteria would be.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 16, 2015 at 5:59 pm
Got it mate thanks for your reply.
What should I do if I want to fail the task if there are duplicates? Do I need to write a separate query to find the duplicates or is there any function to identify?
July 17, 2015 at 7:14 am
As twin.devil already pointed out, if you use MERGE instead of a simple UPDATE, it will automatically raise an error if you try to update the same record twice. Otherwise, you can use an IF statement to check for duplicates and run the update if there are no duplicates and raise an error if there are duplicates.
DECLARE @SampleTable TABLE ( table_id INT, table_date DATE )
INSERT @SampleTable( table_id )
VALUES (1), (1)
IF NOT EXISTS ( SELECT table_id FROM @SampleTable GROUP BY table_id HAVING COUNT(*) > 1)
UPDATE @SampleTable
SET table_date = SYSDATETIME()
ELSE
RAISERROR('Duplicate IDs found. Cannot update.', 16, 1)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply