Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

UPDATE SQL Query Help ! Expand / Collapse
Author
Message
Posted Thursday, February 20, 2014 8:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 47, Visits: 431
Folks:

I need help with a UPDATE SQL Statement.

CREATE TABLE #TEST1
(
AID INT ,
EmailID VARCHAR (200)
);

INSERT INTO #TEST1
VALUES (100, 'jack@microsoft.com');

INSERT INTO #TEST1
VALUES (100, 'david@microsoft.com');

INSERT INTO #TEST1
VALUES (100, 'paul@microsoft.com');

INSERT INTO #TEST1
VALUES (200, 'amy@microsoft.com');

INSERT INTO #TEST1
VALUES (200, 'bill@microsoft.com');

INSERT INTO #TEST1
VALUES (300, 'chris@microsoft.com');

INSERT INTO #TEST1
VALUES (400, 'katrina@microsoft.com');

INSERT INTO #TEST1
VALUES (500, 'john@microsoft.com');

INSERT INTO #TEST1
VALUES (500, 'jack@microsoft.com');

INSERT INTO #TEST1
VALUES (500, 'david@microsoft.com');

CREATE TABLE #TEST2
(
AID INT ,
EmailIDs VARCHAR (1000) NULL
);

INSERT INTO #TEST2 (AID)
VALUES (100);

INSERT INTO #TEST2 (AID)
VALUES (200);

INSERT INTO #TEST2 (AID)
VALUES (300);

INSERT INTO #TEST2 (AID)
VALUES (400);

INSERT INTO #TEST2 (AID)
VALUES (500);



I would like to update the Email IDs column in #TEST2 with Email ID value from #TEST1 table for matching AID column. Instead of having the email ids on seperate row I would like the email Id for each AID in a single column seperated with semi colon(;). Any help on update SQL is appreciated.

OUTPUT:

AID EmailIDs
100 jack@microsoft.com; david@microsoft.com; paul@microsoft.com
200 amy@microsoft.com; bill@microsoft.com
300 chris@microsoft.com
400 katrina@microsoft.com
500 john@microsoft.com; jack@microsoft.com; david@microsoft.com



Thanks !
Post #1543516
Posted Thursday, February 20, 2014 8:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:03 PM
Points: 3,584, Visits: 8,046
You shouldn't store lists for delimited values in a single line for a single value. It violates the first normal form.
If you really need this (I suggest to use it only for display or email generation purposes), you could use the method described in this article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
This is really fast and you don't need to store the values as arrays.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1543530
Posted Thursday, February 20, 2014 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 6,381, Visits: 13,724
sorry wrong code

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1543536
Posted Thursday, February 20, 2014 8:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 47, Visits: 431
It is only for display purpose in reporting because the user doesn't want to see 4 rows for each record if there are 4 email ids for that AID.
Post #1543550
Posted Thursday, February 20, 2014 8:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:03 PM
Points: 3,584, Visits: 8,046
sqldba20 (2/20/2014)
It is only for display purpose in reporting because the user doesn't want to see 4 rows for each record if there are 4 email ids for that AID.


So, do you need more help? Or can you find a solution from the article on your own?If you get the solution on your own, you'll learn more than getting the answer already processed.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1543554
Posted Thursday, February 20, 2014 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
sqldba20 (2/20/2014)
It is only for display purpose in reporting because the user doesn't want to see 4 rows for each record if there are 4 email ids for that AID.


You may find it more efficient to perform this relatively simple operation in the code which generates the #temp table, rather than updating the temp table afterwards. If you're not sure how to do this, post the code and we'll help.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1543555
Posted Thursday, February 20, 2014 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 47, Visits: 431
Nevermind...I was able to figure out using the link posted by Luis. Came with this SQL and it works.

    WITH     Ranked (AID, rnk, EmailID)
AS (SELECT AID,
ROW_NUMBER() OVER (PARTITION BY AID ORDER BY AID),
CAST (EmailID AS VARCHAR (8000))
FROM #TEST1),
AnchorRanked (AID, rnk, EmailID)
AS (SELECT AID,
rnk,
EmailID
FROM Ranked
WHERE rnk = 1),
RecurRanked (AID, rnk, EmailID)
AS (SELECT AID,
rnk,
EmailID
FROM AnchorRanked
UNION ALL
SELECT Ranked.AID,
Ranked.rnk,
RecurRanked.EmailID + '; ' + Ranked.EmailID
FROM Ranked
INNER JOIN
RecurRanked
ON Ranked.AID = RecurRanked.AID
AND Ranked.rnk = RecurRanked.rnk + 1)
SELECT AID AS AID,
MAX(EmailID) AS EmaiID
FROM RecurRanked
GROUP BY AID

Post #1543563
Posted Thursday, February 20, 2014 9:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:03 PM
Points: 3,584, Visits: 8,046
sqldba20 (2/20/2014)
Nevermind...I was able to figure out using the link posted by Luis. Came with this SQL and it works.

How on earth did you figure that out from the link I posted?
This method might kill the performance as it will create more rows than needed.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1543590
Posted Thursday, February 20, 2014 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
Luis Cazares (2/20/2014)
sqldba20 (2/20/2014)
Nevermind...I was able to figure out using the link posted by Luis. Came with this SQL and it works.

How on earth did you figure that out from the link I posted?
This method might kill the performance as it will create more rows than needed.


Baffled me too, Luis.
rCTE's aren't the best choice for this - slower than FOR XML PATH, waaaaay overcomplicated.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1543596
Posted Thursday, February 20, 2014 10:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 47, Visits: 431
If this is not appropriate then could you help me a updated SQL ?
Post #1543635
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse