• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

delete sql

 
Jackie Wang
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i run:
select * from
emp a, empno b
whre a.empname = b.empname
and it return some result set.
what i should do it delete these record?
if i use
delete from
emp a, empno b
whre a.empname = b.empname
Error: ORA-00933: SQL command not properly ended
thanks!
 
Rajeshwari Natarajan
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
U can't perform DML(insert/update/delete) operations on more than one table at a time..even if u are referring to the same table by means of alias.
I think what u intend to do is delete duplicate records.
If that is the case, u can try the following..

DELETE FROM EMP A WHERE ROWID > (
SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNAME = B.EMPNAME);

regards
Rajeshwari
 
Rajeshwari Natarajan
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
u can try this..
DELETE FROM EMP A WHERE A.EMPNAME IN (SELECT B.EMPNAME FROM EMPNO B)
 
Jackie Wang
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks
Rajeshwari
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic