Why is this T_SQL update not working ??, Please help to understand

Question

Why is this T_SQL update not working ??, Please help to understand

Mathew Abraham

SSC Journeyman

Points: 86
More actions
March 22, 2012 at 3:18 pm

#255178

Declare @table_S table (i int, j int,k int) Declare @table_D table (i
int, j int,k int)
Insert into @table_S
Select 2,5,null union all
Select 2,4,7
Insert into @table_D
Select 2,8,3
Select * from @table_D
Select * from @table_S
Update D set j=s.j, k=s.k from @table_D D,@table_S s where d.i=s.i
Select * from @table_D

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What is your expected output and what are you getting. I cut/past the code and it appears to work fine for me. Not sure if the results are accurate.

Mathew Abraham SSC Journeyman Points: 86 More actions · Answer 2

result in @Table_D, should either be 2,5,NULL or 2,4,7 , but why is it 2,5,7

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I can't answer this myself. I can get the answer 2,4,7 if I flip the order of the inserts in the the table @s-2 such that 2,4,7 is inserted first.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Mathew Abraham (3/22/2012)
Update D set j=s.j, k=s.k from @table_D D,@table_S s where d.i=s.i

The problem is the CROSS JOIN. why do u want to do a cross join to update?

What is it exactly that you are trying to perform?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ColdCoffee (3/22/2012)
Mathew Abraham (3/22/2012)
Update D set j=s.j, k=s.k from @table_D D,@table_S s where d.i=s.i
The problem is the CROSS JOIN. why do u want to do a cross join to update?
What is it exactly that you are trying to perform?

Not a cross join, ANSI-89 style join, join criteria is in the where clause.

I get the same behaviour when written using an ANSI-92 style join.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Hmmm, that IS really odd.

You can't guarantee the order of the updates but I wouldn't expect the null to be ignored.

Wrapping the k setting with an isnull also doesn't correct the issue. Oddly, there's a stream aggregate (SQL 2k5/2k8) in the execution plan, which is working off the bookmark.

Replacing the NULL with 1 removes the 'strangeness'. The Stream Aggregate continues to exist, however the entire update is from the same record.

I can only assume the aggregate component is carrying data from 'later' in the update to the final update and replacing nulls. Including a new SELECT of 2,9,8 doesn't affect the results if you include it at the tail of the Insert Into. Include that select at the beginning and it alters the results to be only the first record.

It's doing some kind of rollup. I'd never realized it did that.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Mathew Abraham SSC Journeyman Points: 86 More actions · Answer 7

Result is not important, but an insight on what sqlserver tries to do internally when we execute the query. How does it arrive at 2,5,7 when the first row has a Null value in the field k

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

ColdCoffee (3/22/2012)
Mathew Abraham (3/22/2012)
Update D set j=s.j, k=s.k from @table_D D,@table_S s where d.i=s.i
The problem is the CROSS JOIN. why do u want to do a cross join to update?
What is it exactly that you are trying to perform?

It's not the cross join. Check this:

Declare @table_S table (i int, j int,k int)

Declare @table_D table (i int, j int,k int)

Insert into @table_S

SELECT 2,9,8

UNION ALL

Select 2,5,NULL

union all

Select 2,4,7

Insert into @table_D

Select 2,8,3

Select * from @table_D

Select * from @table_S

Update D

set

j = s.j,

k = isnull( s.k, 0)

from

@table_D D

JOIN@table_S s

ONd.i=s.i

Select * from @table_D

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Has something to do with the null value. Change it an integer value you get the first values inserted into @S.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 10

Lynn Pettis (3/22/2012)
ColdCoffee (3/22/2012)
Mathew Abraham (3/22/2012)
Update D set j=s.j, k=s.k from @table_D D,@table_S s where d.i=s.i
The problem is the CROSS JOIN. why do u want to do a cross join to update?
What is it exactly that you are trying to perform?
Not a cross join, ANSI-89 style join, join criteria is in the where clause.
I get the same behaviour when written using an ANSI-92 style join.

My bad, dint see the WHERE clause.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 11

I believe this thread is on the same subject:

http://www.sqlservercentral.com/Forums/Topic1260577-338-1.aspx#bm1260601

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

dwain.c (3/22/2012)
I believe this thread is on the same subject:
http://www.sqlservercentral.com/Forums/Topic1260577-338-1.aspx#bm1260601

Looks the same but two different people? Could be a class assignment.