Simple scenario - 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

Simple scenario

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-01 : 21:26:51

I have my_table with 4 columns.
col1 = ID (identity)
col2 = categoryid (A101,A102,M103,.....n)
col3 = code (anyvalue) (should be same for same categoryid)
col4 = status (fail if code is different for same categoryid,else NULL)

example:
ID CategoryID Code Status
1 A100 xx12 NULL
2 A100 xx12 NULL
3 A100 xx55 FAIL (code should be xx12)
4 A100 xx12 NULL
5 B201 qq16 NULL
6 B201 qq16 NULL
7 B201 qq21 FAIL (code s/b qq16)
8 B201 qq24 FAIL (code s/b qq16)

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-01 : 21:53:06

What is your question ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 23:55:46

you want us to even guess the question?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-02 : 15:25:54

sorry, question is to update the col4 = status according to the scenario. (it is null originally)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 20:25:33

isnt this same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165006

please dont cross post

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-06 : 15:19:16

No, Its a totally different question.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-06 : 17:14:12

WITH CTE_Multiple AS
(SELECT ROW_NUMBER() OVER (PARTITION BY categoryID, code,
ORDER BY (SELECT 0)) RN,Status
FROM my_table
)
UPDATE CTE_Multiple
SET Status = 'FAILURE'
WHERE RN=1

This is giving me the first and the actual 'failure'
I just want the actual 'failure'

Thanks

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-07 : 01:17:00

how do u determine which one is correct? that is xx12 is correct or xx55 is correct.? do you have any master table for codes?

Karthik
http://karthik4identity.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources