Home » Developer & Programmer » Forms » Urgent Urgent
Urgent Urgent [message #82275] Wed, 14 May 2003 23:06 Go to next message
Sam
Messages: 255
Registered: April 2000
Senior Member
Please can anybody help me out

suppose there is column "deptno"and i want to delete only those rows which has deptno=90,
so can anybody giv me the syntax of how to delete rows for a particular column.

sam

Report message to a moderator

Re: Urgent Urgent [message #82279 is a reply to message #82275] Thu, 15 May 2003 00:59 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
SQL forum.

Basically it is:
DELETE FROM dept d
 WHERE rowid <> ( SELECT MIN(rowid)
                    FROM dept
                   WHERE deptno = d.deptno )
This statement will delete all records but one for all deptno values. The principle is: we need to uniquely identify each record (even though they're duplicates). For that, we can use ROWID. So if we say that we want to delete all duplicates which have a different rowid than the one with the smallest rowid, the latter won't be deleted. In our example, duplicates are identified by the deptno column so we evaluate that in the inner select. If there are more criteria, e.g. dname, we need to add "AND dname = d.dname" to the inner select.

MHE

Report message to a moderator

Previous Topic: Interview questions for oracle d2k
Next Topic: Deploying Oracle forms
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 15:31:10 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.