Coalesce then update? Help - 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 2005 Forums

Transact-SQL (2005)

Coalesce then update? Help

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2009-08-12 : 04:35:16

I have two tables, customers and pets. One customer can (of course) have many pets.

I am trying to use coalesce to concatenate the names of all the pets [petName] in table2 who have the same customerID, and then update the column [PetNames] in table1 with the concatenated string.

table1=dbo.customers
customerID
CustomerName
PetNames

table2=dbo.pets
petID
customerID
petName

I need this query to run either (a) as a stored procedure when a new pet record is added to table2 and/or (b) as an update query when something changes in a pet record.

I am very new to SQL - very! Can anybody please help???

Thanks

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-12 : 04:47:07

Hi try this once


update c
set petname= stuff(( select ','+petname from pets 
                      where customerid = c.customerid for xml path('')),1,1,)
from customer c

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-12 : 04:47:15

can u post some sample data and required output
then it is clear for us

nick_dkc
Starting Member

25 Posts

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

Thank you guys.

dbo.customers
custID Name PetNames
-------------------------
1 Smith
2 Jones
3 Harris

dbo.pets
petID custID PetName
-------------------------
1 1 Smelly
2 1 Hairy
3 1 Nasty
4 2 Nice
5 2 Sweet
6 3 Ginger
7 3 Milord

The PetNames column in dbo.customers will be NULL when the customer record is inserted. Pets are added later as part of another process.

I don't know if this should be an SP or what. Anyway, the required output should be in dbo.Customers as shown below.

dbo.customers
custID Name PetNames
-----------------------------------
1 Smith Smelly, Hairy, Nasty
2 Jones Nice, Sweet
3 Harris Giner, Milord

Greatly appreciate the help and assistance!

Nageswar9 - I will try your snippet. But I don't understand "stuff" :-)

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-12 : 04:59:45

CREATE TABLE dbo.customers
(
customerID INT IDENTITY(1,1),
CustomerName VARCHAR(50),
PetNames VARCHAR(MAX) NULL
)
CREATE TABLE dbo.PETS
(
petID int IDENTITY(1,1),
customerID INT,
PetName VARCHAR(50)
)
INSERT INTO customers
SELECT 'POP',NULL
UNION ALL
SELECT 'POP1',NULL
UNION ALL
SELECT 'POP2',NULL
UNION ALL
SELECT 'POP4',NULL

INSERT INTO PETS
SELECT 1,'A'
UNION ALL
SELECT 2,'B'
UNION ALL
SELECT 3,'C'
UNION ALL
SELECT 1,'D'
UNION ALL
SELECT 1,'E'
UNION ALL
SELECT 2,'G'
UNION ALL
SELECT 3,'G'
UNION ALL
SELECT 2,'H'
UNION ALL
SELECT 3,'I'

CREATE FUNCTION DBO.FETCHSTRING
(@customerID int)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=COALESCE(@SQL + ',', '') + petName From PETS Where customerID=@customerID
RETURN @SQL
END

Update c
Set PetNames= DBO.FETCHSTRING(c.customerID)
From customers c

--------------------
Rock n Roll with SQL

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-12 : 05:02:52

quote:
Originally posted by nick_dkc

Thank you guys.

dbo.customers
custID Name PetNames
-------------------------
1 Smith
2 Jones
3 Harris

dbo.pets
petID custID PetName
-------------------------
1 1 Smelly
2 1 Hairy
3 1 Nasty
4 2 Nice
5 2 Sweet
6 3 Ginger
7 3 Milord

The PetNames column in dbo.customers will be NULL when the customer record is inserted. Pets are added later as part of another process.

I don't know if this should be an SP or what. Anyway, the required output should be in dbo.Customers as shown below.

dbo.customers
custID Name PetNames
-----------------------------------
1 Smith Smelly, Hairy, Nasty
2 Jones Nice, Sweet
3 Harris Giner, Milord

Greatly appreciate the help and assistance!

Nageswar9 - I will try your snippet. But I don't understand "stuff" :-)

update c
set petname= stuff(( select ','+petname from pets
where customerid = c.customerid for xml path('')),1,1,'')
from customer c

i used stuff, bcoz in the petname column, values are updated ,pet1,pet2,pet3 like wise, to remove ',' in the first i used stuff

nick_dkc
Starting Member

25 Posts

Posted - 2009-08-12 : 05:25:46

Nageswar9 - you are a star that works brilliantly!

Final question then - I already have 2 triggers on my pets table. Can I just add this new code to the existing 'after insert' and 'after update' triggers.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_updatePetDetails] ON [dbo].[PetDetails]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;

UPDATE dbo.PetDetails
SET Breed_name =
CASE
WHEN (breed_pure IS NOT NULL) AND (breed_cross IS NOT NULL) THEN breed_pure + ' X ' + breed_cross
WHEN (breed_pure IS NOT NULL) AND (breed_cross IS NULL) THEN breed_pure
WHEN (breed_pure IS NULL) AND (breed_cross IS NULL) THEN NULL
END

UPDATE dbo.Customers
SET petnames = stuff(( select ', '+ petname from petDetails
WHERE customerid = c.customerid for xml path('')),1,1,'')
FROM customers c

END

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-12 : 13:01:53

Since you can get this comma separated list with a select, why would you add it as a column in your customers table?
You are defeating the purpose of having a RELATIONAL DATABASE.
Might as well be using excel.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
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

nick_dkc
Starting Member

25 Posts

Posted - 2009-09-24 : 03:39:02

Hi DonAtWork, just been back into the forum and saw your response - I appreciate the sentiment, sincerely and I don't have a good reply to you 'cos I don't know enough!

(I actually went back in to ask how to programmatically add the "&" before the last petName that is found by the query - so that the output is more 'proper text friendly' for use in forms and our application interface)

We use this piece of data often as it is called into the application we are using - my thinking was that it therefore needed to be stored somewhere and I needed it to be available as soon as the user inserts or updates an individual pet record (in petDetails table).

Can I ask you - how would you suggest I do this in a better way?

Suggestions from other forum members would also be welcome.

Thanks again.

Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources