March 1, 2012 at 8:20 pm
Please try to run example below to understand the problem.
I am trying to update val1 and val2 columns based on join of table 1 and table 2 on id column.
When values are updated for both column, I see that first val is updated from first row and second value is updated from another row. Both val1 and val2 should either be updated from first row or second row. but that is not the case.
when Sql Server see null value in any column, it picks next value which is not null. but thats wrong as now I am getting data from two different rows.
Can someone explain why this is happening and how to avoid this?
Try to run below code and see the problem.
-----------------------------------------------------------------------
create table #temp
(
id int,
val1 int,
val2 int
)
create table #temp2
(
id int,
val1 int,
val2 int
)
insert into #temp (id, val1) values (1, 1)
insert into #temp values (1, 2 ,3)
insert into #temp2 values (1 , 8 , 8)
select * from #temp
--id val1 val2
----------- ----------- -----------
--1 1 NULL
--1 2 3
select * from #temp2
--id val1 val2
----------- ----------- -----------
--1 8 8
update a
set a.val1 = b.val1,
a.val2 = b.val2
from #temp2 a
join #temp b
on a.id = b.id
select * from #temp2
--id val1 val2
----------- ----------- -----------
--1 1 3
drop table #temp
drop table #temp2
-----------------------------------------------------------------------
March 1, 2012 at 9:01 pm
CoolDown (3/1/2012)
I am trying to update val1 and val2 columns based on join of table 1 and table 2 on id column.When values are updated for both column, I see that first val is updated from first row and second value is updated from another row. Both val1 and val2 should either be updated from first row or second row. but that is not the case.
when Sql Server see null value in any column, it picks next value which is not null. but thats wrong as now I am getting data from two different rows.
I actually quite dint get what your requirements are. Can you post your expected output and the rules for the query?
March 1, 2012 at 9:16 pm
Source Table1
id val1 val2
1 1 NULL
1 2 3
Destination Table before update
id val3 val4
1 8 8
Expected Destination Table after update - Option 1
id val3 val4
1 1 NULL
Expected Destination Table after update - Option 2
id val3 val4
1 2 3
Destination Table after update - Real Output
id val3 val4
1 1 3 <-- This should be null
March 1, 2012 at 9:33 pm
Quite peculiar behavior indeed!
By using a RIGHT JOIN you can make it return option 1. Try it with the OUTPUT statement I've included and you'll see that it seems to only UPDATE the record once.
update a
set a.val1 = b.val1,
a.val2 = b.val2
OUTPUT inserted.*, DELETED.*
from #temp2 a
RIGHT join #temp b
on a.id = b.id
I found that INNER JOIN, LEFT OUTER JOIN both return the same result. RIGHT JOIN and FULL OUTER JOIN also return the same result (option 1).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 1, 2012 at 9:34 pm
you can check this more real example too.
create table #Data
(
COMPANY int,
NAME varchar(50),
SALARY int
)
create table #Random
(
Random_COMPANY int,
Random_NAME varchar(50),
Random_SALARY int
)
insert into #Data (COMPANY, NAME) values (1, 'ABC' )
insert into #Data values (1, 'XYZ', 25000 )
insert into #Random(Random_COMPANY) values (1 )
select * from #Data
select * from #Random
update a
set a.Random_NAME = b.NAME,
a.Random_SALARY = b.SALARY
from #Random a
join #Data b
on a.Random_COMPANY = b.COMPANY
select * from #Random
drop table #Data
drop table #Random
output
---------------
Source Table1
COMPANY NAME SALARY
1 ABC NULL
1 XYZ 25000
Destination Table before update
Random_COMPANY Random_NAME Random_SALARY
1 NULL NULL
Expected Destination Table after update - Option 1
Random_COMPANY Random_NAME Random_SALARY
1 ABC NULL
Expected Destination Table after update - Option 2
Random_COMPANY Random_NAME Random_SALARY
1 XYZ 25000
Destination Table after update
Random_COMPANY Random_NAME Random_SALARY
1 ABC 25000 <-- This should be null
March 1, 2012 at 9:46 pm
This article seems to be relevant:
Haven't found a way to return your option 2!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 1, 2012 at 9:50 pm
something like this, may be?
-- Open a tran
BEGIN TRAN
-- Choose data before update
SELECT * FROM #temp2
UPDATE t2
SET t2.val1 = tCrsApp.val1
, t2.val2 = tCrsApp.val2
FROM #temp2 t2
CROSS APPLY
( -- This will generate a random
-- row from the #temp table
SELECT TOP 1 *
FROM #temp t1
WHERE t1.id = t2.id
ORDER BY NEWID()
) tCrsApp
-- Data after update
SELECT * FROM #temp2
-- Rollback/commit as per the requirement
ROLLBACK TRAN
{Edit - Added comments to the code}
March 1, 2012 at 9:52 pm
I was going to suggest this (similar to ColdCoffee):
update a
set @val1 = a.val1 = b.val1,
@val2 = a.val2 = b.val2
OUTPUT inserted.*, DELETED.*
from #temp2 a
join (
SELECT TOP 1 * FROM #temp ORDER BY val2 DESC) b
on a.id = b.id
If you have some field, like a DATETIME you can order the #temp table on that controls which record you want applied, you should be set.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 2, 2012 at 11:06 am
Thanks!!
I am trying to understand why SQL Server is updating records using two different rows? My thinking was in any case it gets all columns updated from one row.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply