CODEWITHSUNDEEP
sqldatabasesqliterow-number
Fulyze Chan
Fulyze Chan

Reputation: 13

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

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?

Upvotes: 0

Views: 1470

Answers (2)

Tushar
Tushar

Reputation: 3633

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);

Upvotes: 1

davie
davie

Reputation: 26

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user) AS RowNum
  FROM SavedCarts
)
DELETE FROM CTE
WHERE user = 1 AND RowNum = 3;
  • CTE creates a temporary result set with an additional RowNum column, which is the row number for each user
  • Then delete the row you want by specifying the user and RowNum values in the WHERE clause of the DELETE statement

Upvotes: 1

Related Questions