Equivalent of Oracle’s RowID in MySQL

is there an equivalent of oracle’s rowid in mysql?

delete from my_table where rowid not in (select max(rowid) from my_table group by field1,field2)

I want to make a mysql equivalent of this query!!!

What i’m trying to do is, : The my_table has no primary key.. i’m trying to delete the duplicate values and impose a primary key (composite of field1, field2)..!!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

In MySql you usually use session variables to achive the functionality:

SELECT @rowid:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c3fe83b1acb4aaa7">[email protected]</a>+1 as rowid
FROM table1, (SELECT @rowid:=0) as init
ORDER BY sorter_field

But you can not make sorts on the table you are trying to delete from in subqueries.

UPD: that is you will need to create a temp table, insert the ranging subquery to the temp table and delete from the original table by joining with the temporary table (you will need some unique row identifier):

CREATE TEMPORARY TABLE duplicates ...

INSERT INTO duplicates (rowid, field1, field2, some_row_uid)
SELECT
  @rowid:=IF(@f1=field1 AND @f2=field2, @rowid+1, 0) as rowid,
  @f1:=field1 as field1,
  @f2:=field2 as field2,
  some_row_uid
FROM testruns t, (SELECT @rowid:=NULL, @f1:=NULL, @f2:=NULL) as init
ORDER BY field1, field2 DESC;

DELETE FROM my_table USING my_table JOIN duplicates
  ON my_table.some_row_uid = duplicates.some_row_uid AND duplicates.rowid > 0

Since that is one time operation, this should not bring too much overhead.

Method 2

Maybe, I am misreading the question but your query (even in Oracle) doesn’t accomplish your desired goal:

delete from my_table where rowid not in (select max(rowid) from 
my_table group by field1,field2)

MySQL equivalent is

SELECT @rowid:=max(rowid) from my_table;
DELETE FROM my_table where rowid != @rowid;

This will wipe out all rows except for last one.

To perform one time cleanup (removing duplicate records) of your data you can do this:

CREATE TABLE my_table2 SELECT distinct f1, f2, f3, etc from my_table;
DROP TABLE my_table;
ALTER TABLE my_table2 RENAME my_table;

Then add whatever columns & keys necessary by ALTER TABLE. Above code might require to drop any foreign keys you might have.

Method 3

mysql> set @row_num = 0;

First set rowId or row num the use it as following:

mysql>  SELECT @row_num := @row_num + 1 as row_number,id,name,salary FROM employee
ORDER BY salary;

Method 4

you can avoid the temp table using another derived table:

DELETE FROM my_table USING my_table JOIN (
    SELECT @rowid:=IF(@f1=field1 AND @f2=field2, @rowid+1, 0) as rowid,
        @f1:=field1 as field1,
        @f2:=field2 as field2,
       some_row_uid
   FROM testruns t, (SELECT @rowid:=NULL, @f1:=NULL, @f2:=NULL) as init
   ORDER BY field1, field2 DESC) as duplicates
ON my_table.some_row_uid = duplicates.some_row_uid AND duplicates.rowid > 0

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0