October 27, 2012 at 9:59 am
Capnhector,
So I got what I needed however I need to update a field in Tbldoc (maybe called as UPDATEDTO).
What I can see in the last statement(below) is more like a SELECT statement and it returns values from tables Parse2 and p1. There is a relationship between these two tables and the tbldoc which is p1.ListID=tbldoc.ID. In order to update tbldoc.UPDATEDTO = LTRIM(Stuff((select '; ' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' ')), I need to have p1.ListID=tbldoc.ID somewhere in the below codes, Am I correct? I am having trouble putting in this UPDATE statement. 🙂
Can you please help me with this?
select ListID,
LTRIM(Stuff((select '; ' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' '))
from Parse2 p1
October 29, 2012 at 8:05 am
BriPan (10/29/2012)
why cant go for simple replace
declare @test-2 varchar(100)='<test@gmail.com>;<test1@gmail.com>'
select REPLACE(replace(@test,'>',''),'<','')
it does matter how many email in columns.
what say?
Your test data doesn't match what the OP posted. Yours is just the email address. The original data has the name AND the email address.
From the example of :
John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>
The simple replace would return this:
John Doe1 John.Doe1@abc.com; Mary Doe1 Mary.Doe1@abc.com
That isn't exactly what they wanted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2012 at 10:29 am
vu_tran764 (10/27/2012)
Capnhector,So I got what I needed however I need to update a field in Tbldoc (maybe called as UPDATEDTO).
What I can see in the last statement(below) is more like a SELECT statement and it returns values from tables Parse2 and p1. There is a relationship between these two tables and the tbldoc which is p1.ListID=tbldoc.ID. In order to update tbldoc.UPDATEDTO = LTRIM(Stuff((select '; ' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' ')), I need to have p1.ListID=tbldoc.ID somewhere in the below codes, Am I correct? I am having trouble putting in this UPDATE statement. 🙂
Can you please help me with this?
select ListID,LTRIM(Stuff((select '; ' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' '))
from Parse2 p1
you can use CTE's in update statements as well. here is a simple example
CREATE TABLE test (ID INT, SomeThing DATETIME, SomethingElse VARCHAR(32))
INSERT INTO test (ID,SomeThing)
SELECT N + 1, DATEADD(DD,N,GETDATE())
FROM Tally
WHERE N < 10
SELECT * FROM test;
WITH MakeSomeData AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as ID, CHAR(N) AS NewStuff FROM Tally WHERE N BETWEEN 65 and 75)
UPDATE test SET SomethingElse = NewStuff
FROM test t
INNER JOIN MakeSomeData m
ON t.ID = m.ID
SELECT * FROM test
DROP TABLE test
in the above i used a 0 based tally table to create the table which is the reason for N + 1 in the insert and N < 10. so you may have to modify it a bit. For an explanation on what a tally table is see this link http://www.sqlservercentral.com/articles/T-SQL/62867/
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 29, 2012 at 11:00 am
Thank you capnhector. Look like I have more readings to do. 🙂
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply