Hi Jan, you're right, it's different, ON UPDATE is a better choice. (We use REPLACE with tables with no foreign keys on it, so we haven't had any problems with the delete).
Regards, Ivan <[email protected]> ezt írta (időpont: 2016. aug. 8., H, 15:22): > 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 > >> > >> > > > >
