{"id":4990,"date":"2024-02-26T16:00:49","date_gmt":"2024-02-26T16:00:49","guid":{"rendered":"https:\/\/codethataint.com\/blog\/?p=4990"},"modified":"2024-02-26T16:31:50","modified_gmt":"2024-02-26T16:31:50","slug":"queries","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/queries\/","title":{"rendered":"Queries"},"content":{"rendered":"

How to Delete duplicate Rows from Table<\/strong> <\/p>\n

In the below table studId 1,6 and 9 is repeated which should be deleted.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
studId<\/th>\nstudentName<\/th>\nage<\/th>\n<\/tr>\n
1<\/td>\nMugil<\/td>\n35<\/td>\n<\/tr>\n
2<\/td>\nVinu<\/td>\n36<\/td>\n<\/tr>\n
3<\/td>\nViju<\/td>\n42<\/td>\n<\/tr>\n
4<\/td>\nMani<\/td>\n35<\/td>\n<\/tr>\n
5<\/td>\nMadhu<\/td>\n36<\/td>\n<\/tr>\n
6<\/td>\nMugil<\/td>\n35<\/td>\n<\/tr>\n
7<\/td>\nVenu<\/td>\n37<\/td>\n<\/tr>\n
8<\/td>\nBanu<\/td>\n34<\/td>\n<\/tr>\n
9<\/td>\nMugil<\/td>\n35<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Below query wont work on MySQL but the format doesn’t change. When you take Max only last occurrence of row would be taken and others would be excluded.<\/p>\n

\r\nDELETE FROM tblStudents TS\r\n WHERE TS.studId NOT IN (SELECT MAX(TSS.studId)\r\n                            FROM tblStudents TSS\r\n                        GROUP BY TSS.studentName, TSS.age)c\r\n<\/pre>\n
The same could be done using MIN function.<\/p>\n
\r\nSELECT TS.* FROM tblStudents TS\r\n WHERE TS.studId NOT IN (SELECT MIN(TSS.studId)\r\n                           FROM tblStudents TSS\r\n                          GROUP BY TSS.studentName, TSS.age)                        \r\n<\/pre>\n
Output<\/strong><\/p>\n\n\n\n\n\n
studId<\/th>\nstudentName<\/th>\nage<\/th>\n<\/tr>\n
6<\/td>\nMugil<\/td>\n35<\/td>\n<\/tr>\n
9<\/td>\nMugil<\/td>\n35<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
How to Delete duplicate Rows from Table In the below table studId 1,6 and 9 is repeated which should be deleted. studId studentName age 1 Mugil 35 2 Vinu 36 3 Viju 42 4 Mani 35 5 Madhu 36 6 Mugil 35 7 Venu 37 8 Banu 34 9 Mugil 35 Below query wont work… Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[362,230],"tags":[],"class_list":["post-4990","post","type-post","status-publish","format-standard","hentry","category-queries-interview-questions-angular","category-interview-questions-database-2"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/4990","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=4990"}],"version-history":[{"count":5,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/4990\/revisions"}],"predecessor-version":[{"id":5001,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/4990\/revisions\/5001"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=4990"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=4990"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=4990"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}