Hello Ivan,
REPLACE INTO is slightly different.
REPLACE works exactly like INSERT, except that if an old row in the
table has the same value as a new row for a PRIMARY KEY or a UNIQUE
index, the old row is deleted before the new row is inserted
(http://dev.mysql.com/doc/refman/5.7/en/replace.html)
I think (not tested tho) this wont work if you have foreign keys on
that table. IMO it wont be equivalent to a MERGE implementation in
Oracle.
- jan
Zitat von Ivan Nemeth <[email protected]>:
Hi Jan,
I agree, it would be useful. We also use some insertOrUpdate mechanism,
currently implemented as pure SQL, but it would be great if we can do it
with Empire.
2 remarks:
1. With MySql we use REPLACE INTO instead ON DUPLICATE..., maybe it's the
same.
2. It's also possible with MSSQL using the MERGE command.
Regards,
Ivan
<[email protected]> ezt írta (időpont: 2016. aug. 8., H, 13:01):
Hello,
I'm currently writing a few sync jobs. The naive approach was to run
an UPDATE first and an INSERT with same DBCommand object if nothing
was updated. This works but is slow.
I figured there is a INSERT ... ON DUPLICATE KEY UPDATE Syntax in
MySQL
(http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html)
which combines INSERT and UPDATE in one single statement. Using this
I'm able to perform a single batch satement instead of single
statements. In my first try it saved 70 % of running time.
My implementation is pretty simple, its just
public synchronized String getInsertOrUpdate()
{
StringBuilder buf = new StringBuilder(getInsert());
buf.append(" ON DUPLICATE KEY UPDATE ");
long context = CTX_NAME | CTX_VALUE;
addListExpr(buf, set, context, ", ");
return buf.toString();
}
in DBCommandMySQL, but to add this in my DBSQLScript I have to cast my
DBCommand to DBCommandMySQL every time.
I think we should add a method to do this in DBCommand with a default
implementation that throws a NotSupportedException. Same in DBDatabase
(executeInserOrUpdate(...). IMO this is a good idea because its
possible in Oracle (using MERGE) and at least Postgres
(https://wiki.postgresql.org/wiki/UPSERT) - and is very useful.
Opinions?
- jan
