blocks|key|4042422|text|您可以使用here中提到的以下方法|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4042423|Select+all+unique+rows
Copy+them+to+a+new+temp+table
Truncate+original+table
Copy+temp+table+data+to+original+table|code-block|syntax|javascript|4042424|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/5882704/6407858^0|5|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can use the below method as mentioned in <a href="https://stackoverflow.com/a/5882704/6407858">here</a>

<pre><code>Select all unique rows
Copy them to a new temp table
Truncate original table
Copy temp table data to original table
</code></pre>

blocks|key|4042472|text|private+void+delete_duplicatesActionPerformed(java.awt.event.ActionEvent+evt)+{++++++++++++++++++++++++++++++++++++++++++++++++++

++++Connection+connection+=+getConnection();
++++try+{
++++++++++++String+querydi+=+"DELETE+t1+FROM+winner_loser+AS+t1+,winner_loser+AS+t2+where+t1.date='"%2Bgetdate1.getText()%2B"'+AND+t2.date='"%2Bgetdate1.getText()%2B"'++AND+t1.k=t2.k+AND+t1.sn>t2.sn++";
++++++++++++pstmt+=+(PreparedStatement)+connection.prepareStatement(querydi);

++++++++++++pstmt.executeUpdate();

++++++++++++JOptionPane.showMessageDialog(null,+"Delete+Duplicates+Successfully+!+Reload+again+pls+!");

++++++++++++pstmt.close();
++++++++++++connection.close();
++++++++}+catch+(SQLException+ex)+{

++++++++}+++++
}++++|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4042473|上面的代码运行良好，可以删除重复的值。|unstyled|4042474|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>private void delete_duplicatesActionPerformed(java.awt.event.ActionEvent evt) { 

 Connection connection = getConnection();
 try {
 String querydi = "DELETE t1 FROM winner_loser AS t1 ,winner_loser AS t2 where t1.date='"+getdate1.getText()+"' AND t2.date='"+getdate1.getText()+"' AND t1.k=t2.k AND t1.sn&gt;t2.sn ";
 pstmt = (PreparedStatement) connection.prepareStatement(querydi);

 pstmt.executeUpdate();

 JOptionPane.showMessageDialog(null, "Delete Duplicates Successfully ! Reload again pls !");

 pstmt.close();
 connection.close();
 } catch (SQLException ex) {

 } 
} 
</code></pre>

The above code run well to delete duplicates values.

blocks|key|4042900|text|取决于你的数据库有多大。如果你想得到一个非常快的结果，最好使用批处理命令。例如，您有一个简单的表，其中包含以下字段:+id、姓名、姓氏和年龄。再次假设您希望删除所有重复的姓名字段，只保留年龄最大的person+(我只是想给出一个示例，以便它具有所有条件)。首先，创建表的模型类(我总是更喜欢定义模型类)。然后，创建所需的getter、setter和最后的构造函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4042901|++public+class+modelRecord+{
++++++++int+id;
++++++++int+age;
++++++++String+name;
++++++++String+surname;
++++++public+modelRecord()+{
++++++++++++super();
++++++++}
++++++public+modelRecord(int+age,+String+name)+{
++++++++super();
++++++++this.age=+age;
++++++++this.name=+name;
++++}
++++public+int+getAge()+{
++++++++return+age;
++++}

++++public+void+setAge(int+age)+{
++++++++this.age=+age;
++++}
//+the+same+for+other+fields
++++}|code-block|syntax|javascript|4042902|创建另一个类作为您的DAO代码。接下来，您可以搜索表以创建记录重复的列表。假设您调用了数据模型modelRecord.class，那么这意味着您必须定义一个函数，该函数返回此类类型的列表：|4042903|public+List<modelRecord>++ListDuplicatedReviewId()+{
+++
+++Connection+con=null;
++++ResultSet+rs=null;
++++List<modelRecord>+list=new+ArrayList<modelRecord>();
++++PreparedStatement+ps=null;


+//+your+code+for+accessing+to+db+++DeleteQuery="SELECT+max(age),+name,+COUNT(*)+countNum+FROM+table_name+GROUP+BY+`name`+HAVING+countNum+>+1";+ps=con.prepareStatement(DeleteQuery);+rs+=+ps.executeQuery();
++++++++++++++++while+(rs.next())+{
++++++++++++++++++++int+maxAge=rs.getInt(1);
++++++++++++++++++++String+name=rs.getString("name");
++++++++++++++++++++modelRecordrec=new+modelRecord(age,+name);
++++++++++++++++++++list.add(rec);
++++++++++++++++}+//+close+all+your+db+connection+resources
return+list;
++++}|4042904|然后，您可以使用此列表在mysql中应用批量删除：|4042905|public+void+DeleteBatch(List<modelRecord>+list)+throws+SQLException+{
++++final+String+deleteQueryBatch="Delete+from+table_name+where+name=?+AND+age+<?";

++++try(Connection+con=//+get+your+connection+to+db+with+batch+command+support)){
++++++++try(PreparedStatement+ps=con.prepareStatement(deleteQueryBatch)){
++++++++
++++++++++++con.setAutoCommit(false);
++++++++
++++++++++++for+(modelRecord+rec+:+list)+{
++++++++++++++++ps.setString(1,+rec.getName()+);
++++++++++++++++
++++++++++++++++ps.setInt(2,+rec.getAge());
++++++++++++++++
++++++++++++++++ps.addBatch();
++++++++++++
++++++++++++}
++++++++++++
++++++++++++
++++++++++++int[]+result+=ps.executeBatch();
++++++++++++int+sum1+=+IntStream.of(result).sum();
++++++++++++System.out.println("PreparedStatement+Batch+executed,+DELETE+done=+"+%2B+sum1);
++++++++++++con.commit();
++++++++
++++++++}+catch+(SQLException+e)+{
++++++++++++con.rollback();
++++++++++++System.out.println(e);
++++++++}
++++}
++++
}|4042906|最后，您需要一个类来调用这两个方法。我希望我已经解释清楚了。|4042907|现在是第二种方法，以防你的表没有很多记录。执行普通的内连接删除，并且不需要批处理命令。为了做到这一点，您只需要一个表的副本。我在这里只写了一个查询：|4042908|DeleteQuery="+delete+table1+from+table1+inner+join+table2+on+table1.id=table2.id+where+table1.name=table2.name+and+table1.age<table2.age"|4042909|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Depends on how big is your database. If you want to have a very fast result, it is better to use batch commands. For example you have a simple table with the fields: id, name, surname and age. Again suppose you want to delete all duplicated name fields and keep only the oldest person (I just wanted to give an example so it has all the criteria). First you create a model class of your table (I always prefer to define a model class). Then, create getters , setters and finally constructors that you need.
<pre><code> public class modelRecord {
 int id;
 int age;
 String name;
 String surname;
 public modelRecord() {
 super();
 }
 public modelRecord(int age, String name) {
 super();
 this.age= age;
 this.name= name;
 }
 public int getAge() {
 return age;
 }

 public void setAge(int age) {
 this.age= age;
 }
// the same for other fields
 }
</code></pre>
Crate another class as your DAO codes. Next, you can search your table to create a list of your record duplication. suppose that you called your data model modelRecord.class then it means that you have to define a function which returns a List of this class type:
<pre><code>public List&lt;modelRecord&gt; ListDuplicatedReviewId() {
 
 Connection con=null;
 ResultSet rs=null;
 List&lt;modelRecord&gt; list=new ArrayList&lt;modelRecord&gt;();
 PreparedStatement ps=null;


 // your code for accessing to db DeleteQuery=&quot;SELECT max(age), name, COUNT(*) countNum FROM table_name GROUP BY `name` HAVING countNum &gt; 1&quot;; ps=con.prepareStatement(DeleteQuery); rs = ps.executeQuery();
 while (rs.next()) {
 int maxAge=rs.getInt(1);
 String name=rs.getString(&quot;name&quot;);
 modelRecordrec=new modelRecord(age, name);
 list.add(rec);
 } // close all your db connection resources
return list;
 }
</code></pre>
Then, you can use this list to apply a batch delete in mysql:
<pre><code>public void DeleteBatch(List&lt;modelRecord&gt; list) throws SQLException {
 final String deleteQueryBatch=&quot;Delete from table_name where name=? AND age &lt;?&quot;;

 try(Connection con=// get your connection to db with batch command support)){
 try(PreparedStatement ps=con.prepareStatement(deleteQueryBatch)){
 
 con.setAutoCommit(false);
 
 for (modelRecord rec : list) {
 ps.setString(1, rec.getName() );
 
 ps.setInt(2, rec.getAge());
 
 ps.addBatch();
 
 }
 
 
 int[] result =ps.executeBatch();
 int sum1 = IntStream.of(result).sum();
 System.out.println(&quot;PreparedStatement Batch executed, DELETE done= &quot; + sum1);
 con.commit();
 
 } catch (SQLException e) {
 con.rollback();
 System.out.println(e);
 }
 }
 
}
</code></pre>
Finally you need a class to call these two methods. I Hope I explained Clearly.
Now Second method in case your table does not have a lot of records.
do a normal inner join delete and you do not need batch command. in order to do that you just need a copy of your table.
I write here just the query:
<pre><code>DeleteQuery=&quot; delete table1 from table1 inner join table2 on table1.id=table2.id where table1.name=table2.name and table1.age&lt;table2.age&quot;
</code></pre>

I want to delete the duplicate rows in a table using jdbc. 

I just tried to below code which is deleting all the records in a table

<pre><code> String query="Delete from tests where product_id=20 and product_name='KINDLE001'";
</code></pre>

Is there any way to delete the duplicate records and keep the last duplicate value in table.

Like there is a column in table called S.NO 1,2,3 and 1,2 rows are duplicate then I just want to delete record 1. And keep 2, 3 unique records as It's.

Any leads?

How to delete the duplicate from rows from mysql db using java

数据库

我想使用jdbc删除表中的重复行。我只是试着在下面的代码中删除表中的所有记录   String query="Delete from tests where product_id=20 and product_name='KINDLE001'";有没有办法删除重复的记录，并在表中保留最后一个重复的值？例如，表中有一列名...

问如何使用java从mysql db中删除重复的行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用java从mysql db中删除重复的行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用java从mysql db中删除重复的行
EN