0

Doing some programming with Java on MySQL.

I have a statement that works like

insert into table1 (col1, col2, col3) select col1, col2, col3 from table2 where col4 is null on duplicate key update col2 = values(col2), col3 = values(col3)

table1 and table2 both have col1 as unique

When I put this into a Statement.executeUpdate(SQL), it returns integer 23000ish , however when I simply do select * from table2 where col4 is null it returns only about 10500ish rows.

My question, according to the java Doc, seems that java.sql.Statement.executeUpdate(String sql) works as follow

Returns: either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

am I not getting the number of rows updated? Or is it returning something else like the actual number of statments it transformed itself into?

UPDATE::

to expand my question a bit.

The complete statement actually does this

insert into table1 (col1, col2, col3) select table1.col1, table2.col2, table2.col3 from table1 inner join table2 on table1.col1 = table2.col1 where table1.col4 is null on duplicate key update col2 = values(col2), col3 = values(col3)

because of the inner join from table 1, every rows should be an update, no insert should be performed, I am trying to recreate the MERGE function from Oracle 11g.

But my question still remains, is the executeUpdate method returning something else rather than the updated row count?

Jimmy Chi Kin Chau
  • 153
  • 1
  • 13
  • What does a select count(1) table1 give you? – Hassan Mar 20 '17 at 08:55
  • How many actual records did you end up inserting? – Tim Biegeleisen Mar 20 '17 at 08:57
  • table 1 has about 50000 rows, and select count(1) returns 50000 ish. – Jimmy Chi Kin Chau Mar 20 '17 at 09:00
  • actually no rows were inserted, all are updates, I am trying to perform a Oracle like Merge, or upsert. And according to my logic, it should only updating the 10500ish rows. – Jimmy Chi Kin Chau Mar 20 '17 at 09:00
  • 2
    10500ish sounds about half of 23000ish. Maybe your table already contained those records and the returned value could be made of a number resulting from both 'insert into table1' and 'on duplicate key update'. Try to run the statement on an empty table and see what you get ? – Edd Mar 20 '17 at 09:01
  • No...why would you expect `insert` to be doing an update? You are certainly inserting new records. The `on duplicate` clause just says what happens if there is a duplicate with regard to keys. – Tim Biegeleisen Mar 20 '17 at 09:01
  • yes, thats why the col1 at table 1 and table 2 are unique, I am using them as key for on duplicate update – Jimmy Chi Kin Chau Mar 20 '17 at 09:02
  • This is not a flaw of Java or JDBC, which just reports what the server is sending. If you run the statement directly using SQL tool of choice, what count do you get then? – Andreas Mar 20 '17 at 09:08
  • Have a look at those topics, they may help : http://stackoverflow.com/questions/29702595/what-does-the-java-mysql-executeupdate-returns-for-insert-on-on-duplicate-key and http://stackoverflow.com/questions/27793830/mysql-insert-on-duplicate-key-update-on-java-how-to-differ-inserted-updat – Arnaud Mar 20 '17 at 09:15
  • OK, I tried this, `update table 1 set col2 = 0 where col4 is null` then rerun the statement. HeidiSQL (my client) gave me, affected rows for update = 10696, and affected rows for the insertonduplicatekeyupdate 21392. Yeah the 23000ish looks like double the 10500ish, but its still like 2000 off, and that the JDBC seems returning a different number from HeidiSQL. – Jimmy Chi Kin Chau Mar 20 '17 at 09:16
  • @Berger, Thankyou for your info, cleared things up a bit, however it doesn't explain why I get a return which is more than 2 times the number of affected rows, plus I am actually receiving an odd number, given that every row should simply be an update, I should only receive even numbers as return. – Jimmy Chi Kin Chau Mar 20 '17 at 09:20

0 Answers0