Conditional delete multiple column from a table in sql.

Question

0.00/5 (No votes)

See more:

I have a table and i want to delete only those rocords from the table where WorkType ='Non Production' and no any other workType will associate with that EmployeeCode with same project Name.

EmpCode	ProjectName	Worktype	Duration
101	ABC	Non Production	2
101	ABC	QA	10
102	BCD	Non Production	8
103	BCD	QA	5
104	ABC	Non Production	7
103	ABC	NON Production	5

The result would be like this:-

EMPCode	ProjectName	WorkType	Duration
102	BCD	NON Production	8
101	ABC	QA	10
103	BCD	QA	5
104	ABC	Non Production	7
103	ABC	NOn Production	5

Here you can see that Firstrow from table 1 is getting deleted with EmpCode=101 remaining are as it is in the result . Emp Code=102, 104 and 103 also have Worktype as Non Production but they have another records also underlying with same Emp Code, Same projectname but differnt Worktype .

Please help me and share me the query to solve this Problem.

Posted 21-Feb-12 23:39pm

Dharmenrda Kumar Singh

Updated 22-Feb-12 0:20am

v3

Add a Solution

Comments

OriginalGriff 22-Feb-12 6:00am

I don't see from your sample data that EMPCode 104 has any other records - it is not obvious why it should remain and 102 should be deleted.
Use the "Improve question" widget to edit your question and provide better information.

Dharmenrda Kumar Singh 22-Feb-12 6:22am

Thanks . I had updated the Question and thanks for pointing my mistake.

OriginalGriff 22-Feb-12 7:23am

Um. 104 still looks to be deletable if 102 is. It does not share an Emp code, it does share a project name, and it has a different Worktype.

Dharmenrda Kumar Singh 22-Feb-12 7:26am

We have to delete only those worktype which have another record with same EmpCode, Same Project name but differnt WorkType.104 Emp Code doest have any other record with same project name,Same EmpCode but different Worktype so we had not deleted it.

Varun Sareen 22-Feb-12 7:46am

it worked??

Dharmenrda Kumar Singh 22-Feb-12 7:48am

yes and Thanks.

Varun Sareen 22-Feb-12 7:57am

welcome

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Varun Sareen · Accepted Answer · 2012-02-22T01:02:00

Solution 1

try this:-

With tb as
(
Select ROW_NUMBER() over(PARTITION by Emp_Code,ProjectName order by emp_code,Worktype desc) 
	as Srn,* From TestEmp 
) Delete From tb Where Srn>1 And WorkType='Non Production'

and reply if it works or not.

Thanks

Posted 22-Feb-12 1:02am

Varun Sareen