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)
 Update Problem

Author  Topic 

rpc86
Posting Yak Master

200 Posts
Posted - 2004-11-10 : 02:31:13
Hi guys,

I need your help with my problem. I have six records below from Ledger Table and I need to update the tblRemarks table from the data given in Ledger. The Remarks column from tblRemarks must concatinate all details from Ledger Table: The result should be like this

tblRemarks

Remarks
------------------------------------------------------------------
MA 07/08/2004 (35000.00), MA 08/08/2004 (35000.00), MA 09/08/2004 (35000.00), LS 10/08/2004 (350000.00), MA 10/08/2004 (35000.00) AND
MA 11/08/2004 (35000.00)





Table Name: Ledger

ItemCode DueDate    UnPaid               
-------- ---------- -------------------- 
MA       07/08/2004 35000.00
MA       08/08/2004 35000.00
MA       09/08/2004 35000.00
LS       10/08/2004 350000.00
MA       10/08/2004 35000.00
MA       11/08/2004 35000.00




This is the S/P I made but the problem is only the last items from Ledger is included.

[code]

Declare @Remarks varChar(8000)
Set @Remarks=''

Update tblRemarks
Set @Remarks=Remarks=@Remarks+' '+rtrim(a.ItemCode) + ' '+Convert(Char(10),a.DueDate,101)+' ('+Rtrim(Ltrim(Cast(a.UnPaid as Char(20))))+')'
From Ledger a Where a.Customer='5175' and isnull(a.UnPaid,0)<>0
and tblRemarks.Customer='5175'

After I run this S/P, I got this result.

tblRemarks

Remarks
-------------------------------------
MA 11/08/2004 (35000.00)


How can this be achieved with the above desired result.

Many thanks.

amachanic
SQL Server MVP

169 Posts
Posted - 2004-11-10 : 09:27:17
I think you'll have to do this one row at a time, unless you write a UDF to concatenate... Write a CURSOR or WHILE loop to get each Customer, then loop over that and concatenate your @Remarks string using:

SET @Remarks = ''

Set @Remarks = @Remarks+' '+rtrim(a.ItemCode) + ' '+Convert(Char(10),a.DueDate,101)+' ('+Rtrim(Ltrim(Cast(a.UnPaid as Char(20))))+')'
From Ledger a Where a.Customer=@Customer and isnull(a.UnPaid,0)<>0

Then you can update your tblRemarks using the same Customer and @Remarks. The iterative UPDATE syntax you're trying to use has some other side effects that aren't desirable in this case (it goes row-by-row), which is why you're only seeing the final value.
Go to Top of Page

amachanic
SQL Server MVP

169 Posts
Posted - 2004-11-10 : 09:28:21
I forgot to mention, you could do this in one step using a scalar UDF:

UPDATE tblRemarks
SET Remarks = dbo.GetRemarks(Customer)

Your UDF would include a parameter for customer, and the same concatentation code I posted above.
Go to Top of Page

rpc86
Posting Yak Master

200 Posts
Posted - 2004-11-10 : 20:29:12
Thanks,

But cursor will suffer our server.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-10 : 20:58:12
Here's a variation on your original that should do the trick:

select customer, duedate, itemcode, unpaid, cast('' as varchar(4000)) as remarks into #data from Ledger
create clustered index #data on #data(customer, duedate, itemcode)

declare @remarks varchar(1000), @cust varchar(5)
select @remarks='', @cust=''
update #data set @remarks=remarks=case when customer=@cust then @remarks else '' end + ' ' +rtrim(ItemCode) + ' '+Convert(Char(10),DueDate,101)+' ('+Rtrim(Ltrim(Cast(UnPaid as varChar(20))))+')', @cust=customer

update t set remarks=(select max(remarks) from #data where customer=t.customer)
from tblRemarks t

drop table #data

This will do the update for all customer codes with one pass through the results, no cursors, no loops. The important thing is the clustered index, make sure the order matches the order in which you want the remarks to be written. I've tested this successfully so if you have trouble with it please reply with the problem.
Go to Top of Page

rpc86
Posting Yak Master

200 Posts
Posted - 2004-11-10 : 21:20:38
Thanks

If I will use temporary table #data, will this not be a problem since this s/p will be used by several users at the same time?
Go to Top of Page

amachanic
SQL Server MVP

169 Posts
Posted - 2004-11-10 : 21:21:13
Rob,

I would still use the scalar UDF in that case, as you can use an ORDER BY clause to guarantee that things are concantenated correctly. It will probably also be more efficient, as you won't have a temp table with one row for every row of the source data.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-10 : 21:37:10
The temp table will be unique for each connection, it's not a global temp table, so you can have 100 concurrent users and they won't have any problems.

As far as a UDF, I'm thinking it will cause a lot more I/O than the temp table would. If the pieces to be concatenated were passed as literals to the UDF then yes, the UDF would be better. But since they're getting values from a table I think the reads will end up costing more. Everything in my example is done in 3 large(r) I/O's rather than many more small(er) ones. I can see the UDF missing the chance to do sequential reads too, the temp table looks like a better bet there. And the clustered index on the temp table will keep the order correctly.

Anyway, either method will work.
Go to Top of Page

amachanic
SQL Server MVP

169 Posts
Posted - 2004-11-10 : 21:48:59
Rob,

Clustered index will not guarantee order in any way. I've seen many counter examples to that myth.

Think page splits, think if the optimizer decides to use another index. Think if the scan isn't done in the order you expect. ORDER BY is the only way to guarantee order.
Go to Top of Page

amachanic
SQL Server MVP

169 Posts
Posted - 2004-11-10 : 21:55:51
Okay, just looked at your example again, I will concede that in this case it might -- but I would still personally never rely on it. Do so at your own risk.
Go to Top of Page

rpc86
Posting Yak Master

200 Posts
Posted - 2004-11-10 : 22:02:40
Many thanks,

I really appreciate your help to me. The code of Robvolk helpmed me a lot. Thank you too amachanic, I'll try your suggestion too.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-10 : 22:08:56
Yeah, you're right about the GUARANTEE of order, but since I'm not adding other indexes, it does work 99.9% of the time.

And of course, once SQL 2005 goes to production it won't work anymore.
Go to Top of Page

amachanic
SQL Server MVP

169 Posts
Posted - 2004-11-10 : 22:41:49
Why won't it work anymore in SQL2005?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-10 : 22:49:19
Note the icon.

Just a gentle dig at MS about changing minor things that break older code. Like BACKUP...WITH STATS in SQL 2000. In 7.0 you'd get progressive messages throughout the backup. In 2000 they all get printed when the backup is done and not before. Not really useful now.
Go to Top of Page

amachanic
SQL Server MVP

169 Posts
Posted - 2004-11-11 : 00:33:19
Not one to give up the chance to get the final word, I've written up my findings after investigating which method is more efficient:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-11 : 08:08:37
Cool!
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts
Posted - 2008-07-25 : 02:34:24
Was browsing some EE questions and came across a question the author answered himself with a link to your blog Adam.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23584640.html

I posted a response you might be interested in.

Rob, what is the final word on the technique you suggested for SQL 2005 (which is what I am using)

I have used, and continue to use, that technique on many occasions for huge data sets when nothing else would have been practical. I have not found a case that did not work yet.



--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

amachanic
SQL Server MVP

169 Posts
Posted - 2008-08-14 : 10:33:13
Hi Ken,

Thanks for the pointer to the experts-exchange thread. Alas, you can't see it without registering and I'm not especially interested in registering for yet another site :-)

Have you seen this article:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

It's the most comprehensive guide to the topic I've yet come across...


---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-08-14 : 12:12:56
quote:
Originally posted by amachanic

Hi Ken,

Thanks for the pointer to the experts-exchange thread. Alas, you can't see it without registering and I'm not especially interested in registering for yet another site :-)

Have you seen this article:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

It's the most comprehensive guide to the topic I've yet come across...


---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net


Good to see one more MVP.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site