How to delete one specific row using Row_Number() in SQLITE?

March 17, 2023

I am new to SQL and I want to be able to perform a query to delete a certain row (NOT duplicated rows) in a table which does not have a PK (for research/learning purpose).

|--------------------------Users---------------------------|
|-ID (PK, AutoIncrement, NN)-|-username(NN)-|-password(NN)-|
|-            1             -|-    abc     -|-     abc    -|
|-            2             -|-    123     -|-     123    -|
|-            3             -|-    qwe     -|-     qwe    -|
|----------------------------------------------------------|

|---------------------------SavedCarts----------------------------|
|- user(FK references Users("id")) -|- cart_content VARCHAR(255) -|
|-               1                 -|- egg,3,milk,4,bread,4      -|
|-               1                 -|- egg,3,milk,1              -|
|-               1                 -|- egg,3,milk,2,cookie,6     -|
|-               2                 -|- egg,3,milk,3              -|
|-               2                 -|- egg,6,milk,5,cereal,5     -|
|-----------------------------------------------------------------|

In this example, the "SavedCarts" table saves the cart’s content of the logged in user. I want to delete for example "row number 3" from the SavedCarts table.

I was able to perform SELECT queries that returns ALL the cart_content of user 1 with the following script:

SELECT ROW_NUMBER() OVER ( PARTITION BY user) RowNum, 
cart_content 
FROM SavedCarts 
WHERE user = 1;

And I was able to perform a SELECT query which returns ONLY a certain cart_content of user 1 with the following script:

SELECT * 
FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user) RowNum FROM SavedCarts ) 
WHERE user = 1 AND RowNum = 3;

However, I could not make it work while implementing the same logic in deleting a specific row in the table (Maybe I did something wrong in the delete script). I know it is way easier to just include a PK in the table but this is for learning purpose. I have been searching online and I can only find others using ROW_NUMBER() to delete duplicate rows(which I tried using their logic as well). Can anyone tell me if deleting a row with ROW_NUMBER() is possible and suggest to me on how to achieve it?

Thank you!

I have tried:

DELETE FROM SavedCarts 
WHERE (user) IN (
   SELECT user FROM (
      SELECT user, ROW_NUMBER() OVER(PARTITION BY user) AS rownum 
      FROM SavedCarts
   ) 
   WHERE user = ? AND rownum = ?
);

DELETE FROM SavedCarts 
WHERE user IN (
   SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER(PARTITION BY user) AS rownum 
      FROM SavedCarts
   ) 
   WHERE rownum = ?
);
(this deletes everything of user 1);

DELETE FROM SavedCarts 
WHERE user = ? AND ROW_NUMBER() OVER(PARTITION BY user) = ?;

Edit:
I have tried CTE, but I am getting this error:
[SQLITE_ERROR] SQL error or missing database (no such table: CTE).
Is this because SQLITE does not support CTE?
Is there a way to achieve this without CTE?

>Solution :

I want to delete for example "row number 3" from the SavedCarts table.

You can do it as :

WITH cte AS (
  SELECT rowid, ROW_NUMBER() OVER (PARTITION BY user ORDER BY rowid) AS RowNum
  FROM SavedCarts
  WHERE user = 1
)
DELETE FROM SavedCarts
WHERE rowid IN (SELECT rowid FROM cte WHERE RowNum = 3);