Home » SQL & PL/SQL » SQL & PL/SQL » Delete using ranking function
Delete using ranking function [message #260203] Fri, 17 August 2007 12:56 Go to next message
munisw1
Messages: 4
Registered: August 2006
Location: Union City, California
Junior Member

The tester can delete the rage of rows from 'dropbox', table. I am quiet not done in my second part of the requirement. And that is pull the first 100 or 200 etc rows from the parent table (user_auth), and delete them from (dropbox). user_auth does not get affected. I able to use rank and dense_rank by create_time against user_auth table, and get the first 100 or 200 rows. However when join with dropbox, for example dropbox.user_id=user_auth.user_id. Query get hunged. I am not sure do i need to write a procedure for this, or a can be acieved by SQL. This is a test env.

SQL> desc user_auth
Name Null? Type
----------------------------------------- -------- -------------
USER_ID NOT NULL NUMBER(10)
USER_NAME NOT NULL VARCHAR2(32)
ACCOUNT_STATUS_CODE NOT NULL NUMBER(3)
PASSWD VARCHAR2(13)
DEVICE_ID_TYPE VARCHAR2(10)
DEVICE_ID VARCHAR2(36)
OLD_MSISDN NUMBER(15)
MASTER_LOCK_CODE VARCHAR2(3)
PARTNER_ID NOT NULL NUMBER(3)
CREATE_TIME NUMBER(10)
MAIL_PROVISIONED NOT NULL NUMBER(1)
PASSWD_TYPE_ID NUMBER(3)

USER_ID is primary key.

INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
PK_USER_AUTH_USER_ID NORMAL UNIQUE
I_USER_AUTH_USER_NAME_PARTNER NORMAL UNIQUE
I_USER_AUTH_DEVICE NORMAL UNIQUE
I_USER_AUTH_MSISDN NORMAL UNIQUE


DROPBOX
Name Null? Type
----------------------------------------- -------- -----------------
USER_ID NOT NULL NUMBER(10)
DROPBOX_ID NUMBER(10)
UPDATE_TIME NUMBER(10)
MESSAGE_TYPE VARCHAR2(64)
MESSAGE BLOB
SURVIVE_REBOOT NUMBER(1)
DELETE_TIME NOT NULL NUMBER(10)

No Primary KEY

INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
I_DROPBOX_USER_ID NORMAL NONUNIQUE
I_DROPBOX_SURVIVE_REBOOT NORMAL NONUNIQUE

If I run

select user_id, create_time, rnk1 FROM
(select user_id, user_name, to_char(convert_unix_time(create_time)) create_time, DENSE_RANK() OVER (ORDER BY user_name) rnk1 from user_auth where user_name LIKE '%stomp%_%')
where rnk1 < 100

I get the result. How do i select these first 100 rows in the dropbox and delete them. relationship is many to many.
Re: Delete using ranking function [message #260206 is a reply to message #260203] Fri, 17 August 2007 13:08 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't understand why you need rank for this. It's a straight forward delete statement
Quote:
delete from dropbox where user_id in (select user_id from (select user_id from user_auth order by user_name) where rownum <= 100)

Also could you please sticky in this forum.

Regards

Raj
Previous Topic: Query Help
Next Topic: problem with utl_smtp_data
Goto Forum:
  


Current Time: Fri Dec 09 15:51:49 CST 2016

Total time taken to generate the page: 0.20217 seconds