Update script - 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

General SQL Server Forums

New to SQL Server Programming

Update script

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-16 : 10:22:15

I want to update RepDocID with adding DocID,If there are same RepID.

declare @tbl1 table
(ID INT,
DocID varchar(255),
RepID INT,
RepDocID varchar (255)
)

INSERT INTO @tbl1
SELECT 1, 'ABC001',1,NULL

INSERT INTO @tbl1
SELECT 101, 'XYZ005',1,NULL

INSERT INTO @tbl1
SELECT 105, 'XYZ011',1,NULL

INSERT INTO @tbl1
SELECT 110, 'XXX111',1,NULL

INSERT INTO @tbl1
SELECT 202, 'ABC001',202,NULL

INSERT INTO @tbl1
SELECT 221, 'XYZ005',202,NULL

INSERT INTO @tbl1
SELECT 555, 'XYZ011',202,NULL

INSERT INTO @tbl1
SELECT 333, 'XXX111',202,NULL

select * from @tbl1

The above sample data will be like this after updating:
declare @tbl1 table
(ID INT,
DocID varchar(255),
RepID INT,
RepDocID varchar (255)
)

INSERT INTO @tbl1
SELECT 1, 'ABC001',1,'ABC001;XYZ005;XYZ011;XXX111'

INSERT INTO @tbl1
SELECT 101, 'XYZ005',1,'ABC001;XYZ005;XYZ011;XXX111'

INSERT INTO @tbl1
SELECT 105, 'XYZ011',1,'ABC001;XYZ005;XYZ011;XXX111'

INSERT INTO @tbl1
SELECT 110, 'XXX111',1,'ABC001;XYZ005;XYZ011;XXX111'

INSERT INTO @tbl1
SELECT 202, 'ABC001',202,'ABC001;XYZ005;XYZ011;XXX111'

INSERT INTO @tbl1
SELECT 221, 'XYZ005',202,'ABC001;XYZ005;XYZ011;XXX111'

INSERT INTO @tbl1
SELECT 555, 'XYZ011',202,'ABC001;XYZ005;XYZ011;XXX111'

INSERT INTO @tbl1
SELECT 333, 'XXX111',202,'ABC001;XYZ005;XYZ011;XXX111'

select * from @tbl1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 10:34:54

Are you using sql 2005?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-16 : 10:43:12

Yes, i am using sql 2005.

quote:
Originally posted by visakh16

Are you using sql 2005?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 11:04:20

[code]
SELECT t.ID,t.DocID,t.RepID,
LEFT(dl.doclist,LEN(dl.doclist)-1) AS RepDocID
FROM @tbl t
OUTER APPLY (SELECT t2.DocID + ';'
FROM @tbl t1
JOIN @tbl t2
ON t1.RepID=t2.RepID
WHERE t1.DocID=t.DocID
AND t1.ID <> t.ID
ORDER BY t2.ID
FOR XML PATH('')
)dl(doclist)
[/code]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-16 : 12:04:38

Thanks,
I tried to customize but it does not work.
I added where condition

SELECT t.ID,t.DocID,t.RepID,
LEFT(dl.doclist,LEN(dl.doclist)-1) AS RepDocID
FROM @tbl t WHERE t.AttStatus = 0 AND (t.DupStatus = 'P' OR t.DupStatus='G')
OUTER APPLY (SELECT t2.DocID + ';'FROM @tbl t1
JOIN @tbl t2 ON t1.RepID=t2.RepID WHERE t1.DocID=t.DocID AND t1.ID <> t.ID
ORDER BY t2.ID
FOR XML PATH('')
)dl(doclist)

quote:

Originally posted by visakh16


SELECT t.ID,t.DocID,t.RepID,
LEFT(dl.doclist,LEN(dl.doclist)-1) AS RepDocID
FROM @tbl t
OUTER APPLY (SELECT t2.DocID + ';'
             FROM @tbl t1
             JOIN @tbl t2
             ON t1.RepID=t2.RepID
             WHERE t1.DocID=t.DocID
             AND t1.ID <> t.ID
             ORDER BY t2.ID
             FOR XML PATH('')
             )dl(doclist)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 12:36:11

[code]
SELECT t.ID,t.DocID,t.RepID,
LEFT(dl.doclist,LEN(dl.doclist)-1) AS RepDocID
FROM @tbl t
OUTER APPLY (SELECT t2.DocID + ';'FROM @tbl t1
JOIN @tbl t2 ON t1.RepID=t2.RepID WHERE t1.DocID=t.DocID AND t1.ID <> t.ID
ORDER BY t2.ID
FOR XML PATH('')
)dl(doclist)
WHERE t.AttStatus = 0 AND (t.DupStatus = 'P' OR t.DupStatus='G')
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources