MySQL Forums
Forum List  »  Oracle

Re: Oracle ROWID equivalent in MySQL
Posted by: David L Lambert
Date: August 04, 2010 07:44AM

(I see it's been a while since you posted this. Sorry if it's too late.)

No, MySQL doesn't expose a ROWID type like Oracle does. It wouldn't even be applicable for some storage engines. However, I can think of a couple of ways to solve your problem:

1. If the table has some sort of timestamp or transaction-ID column that usually identifies only one or a few rows, create a non-unique index on that column. The DELETE statement will use index lookup to find the rows.

2. If you know this won't break anything else that uses the table (no code is doing an INSERT without specifying columns, nor SELECT * FROM ...), add a new primary key column, AUTO_INCREMENT. Reference that key in the data-extraction code.

3. If neither of those is an option (you lack permissions, or the table is on a storage engine like CSV that doesn't support indexes), create a new table with the index or surrogate key, INSERT INTO new_table (...) SELECT ... FROM old_table, and proceed with the ETL operation out of the new table.

4. Better yet, you probably don't need to delete individual the rows in the table at all. Just get all the data out of it, then DELETE without a WHERE clause.

Options: ReplyQuote

Written By
Re: Oracle ROWID equivalent in MySQL
August 04, 2010 07:44AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.