How to convert a SQL statement "remove from a table where someID is not in (select someID from the Table group by property1, property2)

Question

I am trying to convert the following SQL statement to Core Data:

delete from SomeTable
where someID not in (
    select someID
    from SomeTable
    group by property1, property2, property3
)

Basically, I want to get and delete possible duplicates in the table where the record is considered a duplicate if property1, property2 and property3 are equal to another record.

How can i do this?

PS: As the title says, I'm trying to convert the above SQL statement to IOS Core Data methods, not trying to improve, fix or comment on the above SQL, which is beyond the scope.

Thank.

Answer 1

, SQL . , , - , , .

UPDATE

, , , , . SQL, MySQL, , , (property1, property2, property3). , . :

delete from SomeTable st1
where someID not in (
    select min(st2.someId)
    from SomeTable st2
    group by property1, property2, property3
  )

min(), someId . :

delete from SomeTable st1
where someID in (
  select st3.someId
  from SomeTable st2
    join SomeTable st3
      on st2.property1 = st3.property1
        and st2.property2 = st3.property2
        and st2.property3 = st3.property3
  where st2.someId < st3.someId
)

. , , NOT IN . , , , , , (, (property1, property2, property3)) .

Core Data, , , . Core Data , Core Data, , , . , . SO.

, , Core Data , , , . ORM, Core Data. , , ORM.

. SomeTable(property1, property2, property3) , , () .

Answer 2

DELETE SomeTable 
FROM SomeTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, property1, property2, property3 
   FROM SomeTable 
   GROUP BY property1, property2, property3
) as KeepRows ON
   SomeTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Answer 3

iOS: iOS 9 , .. . ( iOS9, NSBatchDeleteRequest, - , , , , , ).

. (. propertiesToGroupBy of NSFetchRequest), NSDictionaryResultType ( , .) , CoreData ( ), GROUP BY. ( ) min(someId). ( , NSExpression, NSExpressionDescription propertiesToFetch ).

, someId (.. , ), . , .

, , . , iOS 9 , ( ).

, , .

Answer 4

:

select t1.someId
      from SomeTable t1
        left outer join SomeTable t2
          on    t1.property1 = t2.property1
            and t1.property2 = t2.property2
            and t1.property3 = t2.property3
            and t1.someId < t2.someId
      where t2.someId  is null;

,

delete SomeTable 
where someId not in
 (select t1.someId
  from SomeTable t1
    left outer join SomeTable t2
      on    t1.property1 = t2.property1
        and t1.property2 = t2.property2
        and t1.property3 = t2.property3
        and t1.someId < t2.someId
  where t2.someId  is null); 

- Sqlfiddle

Answer 5

exists , , , , .

delete from something 
where
    id in (SELECT 
        sm.id
    FROM
        sometable sm

    where
        exists( select 
            1
        from
            sometable sm2

        where
            sm.prop1 = sm2.prop1
            and sm.prop2 = sm2.prop2
            and sm.prop3 = sm2.prop3
            and sm.id != sm2.id)
      );

Answer 6

, , duplicate_flg 1, . , , duplicate_flg= 1. , :

--retrieve all records that has same property values (property1,property2 and property3) 
SELECT *
FROM (
    SELECT someid
        ,property1
        ,property2
        ,property3
        ,CASE 
            WHEN property1 = property2
                AND property1 = property3
                THEN 1
            ELSE 0
            END AS duplicate_flg
    FROM SomeTable
    ) q1
WHERE q1.duplicate_flg = 1;

delete:

DELETE
FROM something
WHERE someid IN (
        SELECT someid
        FROM (
            SELECT someid
                ,property1
                ,property2
                ,property3
                ,CASE 
                    WHEN property1 = property2
                        AND property1 = property3
                        THEN 1
                    ELSE 0
                    END AS duplicate_flg
            FROM SomeTable
            ) q1
        WHERE q1.duplicate_flg = 1
        );

Answer 7

, , Query:

remove from SomeTable where rowid is not in (select max (rowid) from SomeTable group by property1, property2, property3)

Answer 8

if you want to delete all duplicate entries try the code below

WITH tblTemp  as
(
    SELECT ROW_NUMBER() Over(PARTITION BY Property1,Property2,Property3 ORDER BY Property1) As RowNumber,* FROM Table_1
)
DELETE FROM tblTemp where RowNumber >1

Hope this helps

Answer 9

Use the following query to remove duplicate data from this table

remove from SomeTable where someID is not in (select Min (someID) from SomeTable group by property1 + property2 + property3)