Hi! Is there a way to do an upsert only if a certain condition is met (only a field has a certain va...

# komapper

[dave08]

Hi! Is there a way to do an upsert only if a certain condition is met (only a field has a certain value, then it should update it and if the row doesn't exist at all, then it should be inserted...)

[Toshihiro Nakamura]

Yes, Komapper supports that feature. However, it does not work with MySQL, because MySQL does not support it.
https://www.komapper.org/docs/reference/query/querydsl/insert/#onduplicatekeyupdate-where

[dave08]

Oh... is there any other way to do this (even if it's not specifically an upsert, just as long as I'm assured that nothing else modified it between the check for the condition and the actual upsert/update)?

[dave08]

I'm unfortunately running Mysql 5.7 for now, but it's good to know it's implemented for other dbs!

[dave08]

Like maybe some kind of lock on the row until updated?

[dave08]

I don't suppose that query composition would ensure such a thing...?

[Toshihiro Nakamura]

If you want to lock rows, you can use forUpdate .
https://www.komapper.org/docs/reference/query/querydsl/select/#forupdate

[dave08]

1. That works with Mysql 5.7? (with all those options..?)
2. Does that need to be in a withTransaction { ... } along with the upsert? Will the transaction release that lock? Or maybe it's enough just to compose the requests (I only need to upsert if the first query's result contains something)?

[dave08]

Ok, I thought Komapper does things dependent on the dialect behind the scenes... but it seems like it only generates the for update ... in the select, so in mysql there's only for update w/o any variants and no LOCK IN SHARED MODE... (which is the equivilant of one of the options provided that works with postgres...). It would maybe have been better to implement each dialog in the way it works even though it's not the same wording in the QueryDSL...