Update with out cursor - 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 with out cursor

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-25 : 16:14:23

Is there a way to update data without cursor for following data like output (please see output result.)?

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

INSERT INTO @tbl1
SELECT 1,'A001','A001','AAA',NULL

INSERT INTO @tbl1
SELECT 2,'A002','A001','BBB',NULL

INSERT INTO @tbl1
SELECT 3,'A003','A003','CCC',NULL

INSERT INTO @tbl1
SELECT 4,'A004','A003','DDD',NULL

INSERT INTO @tbl1
SELECT 5,'A005','A003','EEE',NULL

INSERT INTO @tbl1
SELECT 6,'A006','A001','FFF',NULL

INSERT INTO @tbl1
SELECT 7,'A007','A007','GGG',NULL

select * from @tbl1

Output looks like this :
ID,Col1,Col2,Col3,Col4
1 A001,A001,AAA,AAA
2 A002,A001,BBB,AAABBB
3 A003,A003,CCC,CCC
4 A004,A003,DDD,CCCDDD
5 A005,A003,EEE,CCCEEE
6 A006,A001,FFF,AAAFFF
7 A007,A007,GGG,GGG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-25 : 16:19:26

Could you explain in words the output?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 16:28:09

This was almost answered in another thread, iquit after the rules changed midstream. rudba, no surprises this time. Give us enough info to solve your problem - a little effort on your part would be helpful since you want us to gift wrap the answer for you. For example, is Col1 just the id + Axxx? What would the concat look like if there were an entry of col1 = 'A005' and col2 = 'A001'?

Thanks,

Jim

Jim

Everyday I learn something that somebody else already knew

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-25 : 16:28:21

Sure Tara.

There are some duplicates data which already exist or matching on Col2,
1)If there is Col1=Col2, just update Col4=Col3
2)if there are more than 1 matching data with Col1's value in the database.
a)Take Col3's value where Col2=Col1 than add this col3 value
the above example data there are 3 A001 and 3 A003.

thanks

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-25 : 16:37:34

Thank you Jimf,

Col1 is a PK field.

Col1='A005' and Col2='A001', it means this is a duplicate data which is referring to Col1='A001'
There are already exist this value see on the top record.

thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 17:32:38

SELECT c1.id,c1.col1,c1.col2,c1.col3,ISNULL(c2.col3 + c1.col3,c1.col3) as Col4
FROM cte c1
LEFT JOIN cte c2 on c1.col2 = c2.col2 and c1.id > c2.id
and c2.recnum < 2
-- and c2.recnum < 2 will need to change when you change your criteria/sample data
-- in your previous post, A006 would have equalled AAABBBFFF

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 19:00:58

Geez, didn't copy/paste the cte but!

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

INSERT INTO @tbl1
SELECT 1,'A001','A001','AAA',NULL

INSERT INTO @tbl1
SELECT 2,'A002','A001','BBB',NULL

INSERT INTO @tbl1
SELECT 3,'A003','A003','CCC',NULL

INSERT INTO @tbl1
SELECT 4,'A004','A003','DDD',NULL

INSERT INTO @tbl1
SELECT 5,'A005','A003','EEE',NULL

INSERT INTO @tbl1
SELECT 6,'A006','A001','FFF',NULL

INSERT INTO @tbl1
SELECT 7,'A007','A007','GGG',NULL

;WITH CTE
AS
(
select id,col1,col2,col3,row_number() over(partition by col2 order by id) as recNum
from @tbl1
)

SELECT c1.id,c1.col1,c1.col2,c1.col3,ISNULL(c2.col3 + c1.col3,c1.col3) as Col4
FROM cte c1
LEFT JOIN cte c2 on c1.col2 = c2.col2 and c1.id > c2.id
and c2.recnum < 2

Jim

Everyday I learn something that somebody else already knew

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-25 : 20:15:45

Jim,

It looks good, but how do i use update for col4?

thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-26 : 07:50:40

Did you read the previous thread you started on this topic? It's time for you to earn your salary. Come up with an update statement, if it doesn't work, post the code here, with whatever error/problem you have and then we can go from there.

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