how to update? - 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)

how to update?

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-20 : 16:27:47

Hello everybody,

I have a data like below structure. I need to update Field3 and Field4.
On Field3, no. of records where are same value in Field1 and Group by Field2.

On field3, no. of records where are same value + Field2 value are different.

declare @tbl1 table
(ID INT,
Field1 INT,
Field2 varchar(10),
Field3 int,
Field4 int
)

INSERT INTO @tbl1
SELECT 1, 1,'AAA',null,null

INSERT INTO @tbl1
SELECT 2,1,'AAA', null,null

INSERT INTO @tbl1
SELECT 3,1,'BBB',null,null

INSERT INTO @tbl1
SELECT 4,1,null,null,null

INSERT INTO @tbl1
SELECT 5, 5,'CCC',null,null

INSERT INTO @tbl1
SELECT 6,5,'DDD', null,null

INSERT INTO @tbl1
SELECT 7,5,'DDD',null,null

INSERT INTO @tbl1
SELECT 8,5,'DDD',null,null

Output Looks like this :

1 1 AAA 2 4
3 1 BBB 1 3
5 5 CCC 1 4
6 5 DDD 3 4

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-20 : 17:58:32

Your output for Field 4 makes no sense and you have no description what it is supposed to be.

This gets you somewhat there:

select MIN(t1.ID) AS ID,
t1.Field1,
t1.Field2,
COUNT(*) AS Field3
FROM @tbl1
AS t1
WHERE t1.Field2
IS NOT NULL
GROUP BY t1.Field1,
t1.Field2

Output:
ID Field1 Field2 Field3
1 1 AAA 2
3 1 BBB 1
5 5 CCC 1
6 5 DDD 3

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-20 : 19:03:04

Lazerath,

Field3 calculation ok, but Field4 makes sense. Look at the row 4 where Field1=1 but Field2=null so, in Field4, it will be Field3's value plus this value.

INSERT INTO @tbl1
SELECT 4,1,null,null,null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 19:45:21

quote:
Originally posted by rudba

but Field4 makes sense.

The point is that it doesn't make sense to us. You know your data, so it makes sense to you.

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

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-21 : 07:23:23

Anybody has idea?

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-21 : 13:38:29

Tara and I are trying to help you but you are not listening. You need to describe in better detail exactly how to calculate Field4 because you have not already done so and your example output for Field4 is non-intuitive and makes no sense to us.

For instance, why does the entry for 'BBB' have a Field4 value of 3 while every other entry has a Field4 value of 4? If you can describe that, it will go a long way.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-21 : 15:38:20

Sorry guys. I will try to explain what i want.
I have a data like below eg. I have to count how many times repetated Field2's record (which is seperated by ; eg. Aaa, Bbb,Ccc ...) where Field1's value has same and Field2 is matching (if Field1 has same but Field2 does not have don't count)--This is for Field3

And Field4, count all field1 matching but not matching Field2 vlaue.
Also need to count if Field1 has same number but Field2 has null dada.

ID Field1 Field2
101,101, Aaa;Bbb;Ccc
102,101, Xyz
103,101,null
104,102,Aaa;Xyz
105,102,Mno
106,102,null

Output
Field2
Aaa 2 3
(in 101 and 104)
Bbb 1 3
Ccc 1 3
Mno 1 3
Xyz 2 3
(in 102 and 104)

In this case Field4 all are 3 because 101 and 102 are 3, 3 records.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-21 : 17:44:03

Ok, first of all, why complicate matters by changing your sample data and data structure? Explaining things in terms of your original question will quicken a successful response.

Secondly, if you can't even supply a correct example, how can we supply a solution? Your output doesn't match your input given what you say:

And Field4, count all field1 matching but not matching Field2 vlaue.
Also need to count if Field1 has same number but Field2 has null dada.

If that was explicitly true, I'd expect the input would expand to this after you apply a Split or ParseValues function:
ID Field1 Field2
101,101, Aaa
101,101, Bbb
101,101, Ccc
102,101, Xyz
103,101, null
104,102, Aaa
104,102, Xyz
105,102, Mno
106,102, null

Which means your output should look like this if what you claim for Field4 is true:
Field2 Field3 Field4 MyComment
Aaa 2 7 (Field1 matches 4 other 101 rows, 3 other 102 rows)
Bbb 1 4 (Field1 matches 4 other 101 rows)
Ccc 1 4 (Field1 matches 4 other 101 rows)
Mno 1 3 (Field1 matches 3 other 102 rows)
Xyz 2 7 (Field1 matches 4 other 101 rows, 3 other 102 rows)

If you didn't apply a Split or ParseValues function, I would expect this to be the result:

Field2 Field3 Field4 MyComment
Aaa;Bbb;Ccc 1 2
Xyz 1 2
Aaa;Xyz 1 2
Mno 1 2

Not trying to be mean, but I can spend no more time on this. Some would say I've spend more time than I should have already. I hope you can rephrase your question properly so someone here can help you further.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources