Warning: Trying to access array offset on value of type null in /home/customer/www/mozartec.com/public_html/wp-content/plugins/pressapps-login-access/includes/skelet/path.php on line 408
Have Rows With Exact Content and No Primary Key ? Can't Delete/Update? - Mozartec
Mozart Al Khateeb

Full Stack

Mobile

.Net Developer

ERP Developer

Mozart Al Khateeb

Full Stack

Mobile

.Net Developer

ERP Developer

Blog Post

Have Rows With Exact Content and No Primary Key ? Can’t Delete/Update?

March 17, 2019 Database
Have Rows With Exact Content and No Primary Key ? Can’t Delete/Update?

When you have no primary key in a table or had it removed (this could occur through migration process) in addition to two exact rows with same content, you won’t be able to update or delete one row without the other. 

The solution is to craft your Own primary to perform the delete/update.

;WITH cte AS (
SELECT ID,
row_number() OVER(PARTITION BY ID ORDER BY ID) AS [craftedPK]
FROM settings
)

Make sure to verify the data before performing the delete / update by selecting the the newly crafted key and data.

SELECT * FROM cte;

Perform the delete.

DELETE cte WHERE [craftedPK] > 1;

I hope you found this helpful.

Taggs:
Write a comment