0

How can i delete a record and have one unique record per user. That means there could be multiple of the same records but by different users. one user can not have more than one of the same record.

For example if user 1 searches for spiderman movie, then that is a unique record, but if they search for spiderman again with the same name no difference then delete the second record. Now if user 2 searches for same spiderman movie then keep that record because the user id of user 2 is different than the user 1 so on so forth.

My question is how can i delete a user record and keep only one unique record per user.

I am using two different tables to track users and match them with the movie table

I would like to delete the movie that is not unique for a user in the user table and movie table.

here are my tables

https://i.stack.imgur.com/yrc97.png

DELETE FROM movie_media t1
            USING accountMovie t2
            WHERE id IN
                (SELECT id
                FROM 
                (SELECT id,
                    ROW_NUMBER() OVER( PARTITION BY title
                ORDER BY id) AS row_num
                FROM movie_media ) t
                WHERE t.row_num > 1)
                AND t2."accountId" = $1

im looking for something like this i know the code will not work im trying to convey my general idea of what im looking for

ZenzMatic
  • 55
  • 2
  • 9
  • Does this answer your question? [Delete duplicate rows from small table](https://stackoverflow.com/questions/6583916/delete-duplicate-rows-from-small-table) – narrowtux Apr 16 '21 at 09:30

1 Answers1

0

The first thing you want to do is check for duplicate records before inserting them. I do this at the code level, but you can also do it at the database level. An ounce of prevention is worth a pound of cure!

If I'm understanding your tables correctly, you should be able to use this to delete your duplicate rows:

DELETE FROM movie
WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY id,
         mediaId
        ORDER BY  id ) AS row_num
        FROM movie ) t
        WHERE t.row_num > 1 );
Tim Powell
  • 127
  • 1
  • 11
  • um seems its not deleting duplicates from either users – ZenzMatic Apr 16 '21 at 02:59
  • its deleting all duplicates i cant make my insertion that have no duplicates because the main table movie has to have all the table data for all users that search for whatever movie they like i would like to have user 1 searches to be all unique so i made a delete route in node to delete only user 1 duplicates search keyword of the a particular movie based on accountId of that user i dont want to have the all the duplicates in movie table by different users to be deleted also just user 1 – ZenzMatic Apr 16 '21 at 03:18