update master and child value - 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)

update master and child value

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-21 : 17:02:51

How do i update Col4, please see output value.
Declare @tbl1 table
(
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar (50),
Col4 varchar (255)
)

INSERT INTO @tbl1
SELECT 'A001','A001','XXXX',NULL

INSERT INTO @tbl1
SELECT 'A002','A002','aaa',NULL

INSERT INTO @tbl1
SELECT 'A003','A002','bbb',NULL

INSERT INTO @tbl1
SELECT 'A004','A002','ccc',NULL

Output :
A001,A001,XXXX,XXXX
A002,A002,aaa,aaa
A003,A003,bbb,aaabbb
A004,A004,ccc,aaabbbccc

reddymrk
Starting Member

6 Posts

Posted - 2011-04-21 : 17:49:44

you can use isnull() function or coalesce to replace the null value with any other value.
Ex: select col1, col2, col3, isnull (col4,'aaaaa') from table1
Ex: select col1, col2, col3, coalesce(col4,'aaaaa') from table1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 18:36:33

Declare @tbl1 table
(
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar (50),
Col4 varchar (255)
)

INSERT INTO @tbl1
SELECT 'A001','A001','XXXX',NULL

INSERT INTO @tbl1
SELECT 'A002','A002','aaa',NULL

INSERT INTO @tbl1
SELECT 'A003','A002','bbb',NULL

INSERT INTO @tbl1
SELECT 'A004','A002','ccc',NULL

SELECT DISTINCT s1.col1,s1.col2,s1.col3,
REPLACE(STUFF((SELECT TOP 100 PERCENT ','+ s2.col3
FROM @tbl1 AS s2
WHERE s2.col2 = s1.col2 and right(s1.col1,1) > right(s2.col1,1) - 1

FOR XML PATH('')),1, 1, '')
, ',','') AS CODES
FROM @tbl1 AS s1

But there's gotta be a more efficient way.

Jim

Everyday I learn something that somebody else already knew

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-21 : 18:50:33

i need to update on Col4, how do i update?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 19:34:24

Declare @tbl1 table
(
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar (50),
Col4 varchar (255)
)

INSERT INTO @tbl1
SELECT 'A001','A001','XXXX',NULL

INSERT INTO @tbl1
SELECT 'A002','A002','aaa',NULL

INSERT INTO @tbl1
SELECT 'A003','A002','bbb',NULL

INSERT INTO @tbl1
SELECT 'A004','A002','ccc',NULL

UPDATE T
SET col4 = codes
FROM
@tbl1 T
INNER JOIN
(
SELECT DISTINCT s1.col1,s1.col2,s1.col3,
REPLACE(STUFF((SELECT TOP 100 PERCENT ','+ s2.col3
FROM @tbl1 AS s2
WHERE s2.col2 = s1.col2 and right(s1.col1,1) > right(s2.col1,1) - 1

FOR XML PATH('')),1, 1, '')
, ',','') AS CODES
FROM @tbl1 AS s1
) t2 on t.col1 = t2.col1

select * from @tbl1

Jim

Everyday I learn something that somebody else already knew

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-22 : 10:40:55

i have a problems please see the result

Declare @tbl1 table
(
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar (50),
Col4 varchar (255)
)

INSERT INTO @tbl1
SELECT 'A001','A001','XXXX',NULL

INSERT INTO @tbl1
SELECT 'A0011','A001','yyyy',NULL

INSERT INTO @tbl1
SELECT 'A002','A002','aaa',NULL

INSERT INTO @tbl1
SELECT 'A003','A002','bbb',NULL

INSERT INTO @tbl1
SELECT 'A004','A002','ccc',NULL

UPDATE T
SET col4 = codes
FROM
@tbl1 T
INNER JOIN
(
SELECT DISTINCT s1.col1,s1.col2,s1.col3,
REPLACE(STUFF((SELECT TOP 100 PERCENT ','+ s2.col3
FROM @tbl1 AS s2
WHERE s2.col2 = s1.col2 and right(s1.col1,1) > right(s2.col1,1) - 1

FOR XML PATH('')),1, 1, '')
, ',','') AS CODES
FROM @tbl1 AS s1
) t2 on t.col1 = t2.col1

select * from @tbl1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 11:03:13

I can only go by the rules and examples you gave me. WHERE s2.col2 = s1.col2 and right(s1.col1,1) > right(s2.col1,1) - 1
will have to change, or you'll have to somehow order the rows exactly as you want them and use the row_number() operator to join on.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources