SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rpc86
Posting Yak Master

200 Posts

Posted - 11/10/2004 :  02:31:13  Show Profile  Reply with Quote
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.



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.

Edited by - rpc86 on 11/10/2004 02:34:31

amachanic
SQL Server MVP

USA
169 Posts

Posted - 11/10/2004 :  09:27:17  Show Profile  Visit amachanic's Homepage  Reply with Quote
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

USA
169 Posts

Posted - 11/10/2004 :  09:28:21  Show Profile  Visit amachanic's Homepage  Reply with Quote
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 - 11/10/2004 :  20:29:12  Show Profile  Reply with Quote
Thanks,

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

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/10/2004 :  20:58:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 11/10/2004 :  21:20:38  Show Profile  Reply with Quote
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

USA
169 Posts

Posted - 11/10/2004 :  21:21:13  Show Profile  Visit amachanic's Homepage  Reply with Quote
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

USA
15676 Posts

Posted - 11/10/2004 :  21:37:10  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
169 Posts

Posted - 11/10/2004 :  21:48:59  Show Profile  Visit amachanic's Homepage  Reply with Quote
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.

Edited by - amachanic on 11/10/2004 21:50:12
Go to Top of Page

amachanic
SQL Server MVP

USA
169 Posts

Posted - 11/10/2004 :  21:55:51  Show Profile  Visit amachanic's Homepage  Reply with Quote
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 - 11/10/2004 :  22:02:40  Show Profile  Reply with Quote
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

USA
15676 Posts

Posted - 11/10/2004 :  22:08:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
169 Posts

Posted - 11/10/2004 :  22:41:49  Show Profile  Visit amachanic's Homepage  Reply with Quote
Why won't it work anymore in SQL2005?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/10/2004 :  22:49:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
169 Posts

Posted - 11/11/2004 :  00:33:19  Show Profile  Visit amachanic's Homepage  Reply with Quote
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

USA
15676 Posts

Posted - 11/11/2004 :  08:08:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
Cool!
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/25/2008 :  02:34:24  Show Profile  Reply with Quote
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

USA
169 Posts

Posted - 08/14/2008 :  10:33:13  Show Profile  Visit amachanic's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/14/2008 :  12:12:56  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000