Incomplete update problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Incomplete update problem

Author

Topic

gilbe73
Starting Member

5 Posts

Posted - 2009-04-07 : 16:28:25

Hello,

here is my short script:

Create Table #tmp(x_val int,y_val char(30),...)
Insert Into #tmp
exec stored_procedure

now my #tmp is filled with 214 rows,
87 of them have the same x_val value(when I use distinct x_val
on #tmp I get 87 rows)

my next step is to try to update another table for each row(214 times)I have in the temporary table:

Update x_table
set field_x =
Case When m1.x_val is null Then m2.x_val
Else m1.x_val + ',' + m2.x_val End
From x_table m1
Inner join #tmp m2
where m1.something = m2.something

My problem is that the update action seems to run only 87 times and not 214 times as the number of rows in the #tmp table.

The m1.x_val should have a value like this in the end: 25,27,30...

Thanks for your help, Gil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-07 : 17:04:24

It is running only 87 times becasue there are only 87 matches in x_table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

gilbe73
Starting Member

5 Posts

Posted - 2009-04-07 : 17:22:20

Can I force it to run 214 times as the number of rows in #tmp?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-07 : 17:32:23

That doesn't make sense since there are only 87 matches.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

gilbe73
Starting Member

5 Posts

Posted - 2009-04-08 : 06:04:47

no, there are 214 rows that match, but 87 of them have the same ID.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-08 : 10:59:51

You're going to need to show us sample data. Show us about 5-10 rows of the 87 and about 5-10 rows of the missing 214.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

gilbe73
Starting Member

5 Posts

Posted - 2009-04-08 : 12:12:08

#tmp table has rows like this:

id val_1 val_2
1 25 26
1 42 31
2 55 25
3 60 30

Second table has this row:
id val_1 val_2
1 42 31
2 55 25
3 60 30

I want the update action run 4 times as the number of rows in the #tmp
so row with ID 1 in the second table will be update twice.

hope it's more clearly now

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-08 : 13:58:21

Clear as mud. Follow the first link in my signature, and then restate your question with the information that is asked for.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

gilbe73
Starting Member

5 Posts

Posted - 2009-04-14 : 17:22:49

I will try to explain myself better.

this is my tmp table:
ID val_1 val_2
1 somthing 26
1 somthing 31
2 somthing 25
3 somthing 30
1 somthing 79

Second table has this row:
ID val_1 val_2
1 somthing null
2 somthing null
3 somthing null

after this update:
Update _table
set val_2 =
Case When m1.val_2 is null Then m2.val_2
Else m1.val_2 + ',' + m2.val_2 End
From _table m1
Inner join #tmp m2
where m1.ID= m2.ID

The second table _table should look like this afeter the update:
ID val_1 val_2
1 somthing 26,31,79
2 somthing 25
3 somthing 30

but what I get is:
1 somthing 79
2 somthing 25
3 somthing 30

row with ID = 1 is update only once with the last value.

dsindo
Starting Member

45 Posts

Posted - 2009-04-14 : 19:44:26

it did update ex. id=1 something 3 times that is why you have the value of the last matching record ID=1.
you have distinct ID rows in #tmp table and multiple ID rows in the _table.

i assume you have
1 Something 26
1 something 31
1 something 79

on you _table.

first 1 something val 2 was updated to 26,
then 1 something val 2 was updated to 31,
then 1 something val 2was updated to 79

thats is why you have 79. the update query will not create the other records for you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-16 : 19:56:58

It's been a while since I've used SQL2000 so I don;t thinkyou can use the FOR XML concatenation method. I think you need to use a UDF in order to do this reasonably effecently. Something like:

CREATE FUNCTION dbo.ConcatVal (@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)	
	SET @Output = ''	
	
	SELECT @Output =	
		CASE @Output 
			WHEN '' THEN CAST(val2 AS VARCHAR(10))
			ELSE @Output + ',' + CAST(val2 AS VARCHAR(10))
		END	
	FROM MyTable	
	WHERE ID = @ID 
	
	RETURN @Output
END

Then you can do something like this to update each row in your other table:

UPDATE #Table2
SET val2 = dbo.ConcatVal(id)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources