Update multiple rows with independent conditions in SQLITE-CodePudding

@Query("UPDATE  RealEstateDatabase SET type = :entryType   WHERE id = :id AND type NOT LIKE :entryType")
suspend fun updateRealEstate(entryType: String, id: String)

This code works perfectly to update a row in the table based on a condition I want to do the same thing with several rows each linked to a condition

here is the entity/table in question

@Entity
@Parcelize
data class RealEstateDatabase(
@PrimaryKey
var id: String,
var type: String? = null,
var price: Int? = null,
var area: Int? = null,
var numberRoom: String? = null,
var description: String? = null,
var numberAndStreet: String? = null,
var numberApartment: String? = null,
var city: String? = null,
var region: String? = null,
var postalCode: String? = null,
var country: String? = null,
var status: String? = null,
var dateOfEntry: String? = null,
var dateOfSale: String? = null,
var realEstateAgent: String? = null,
var lat: Double ?=null,
var lng: Double ?=null,
var hospitalsNear : Boolean = false,
var schoolsNear : Boolean = false,
var shopsNear : Boolean = false,
var parksNear : Boolean = false,
@ColumnInfo(name = "listPhotoWithText")
var listPhotoWithText : List<PhotoWithTextFirebase> ?=null,
var count_photo : Int? = listPhotoWithText?.size,
)

I also put the method of my repository , this method has parameters of the same type as my table to modify

override suspend fun updateRealEstate(
    id: String,
    entryType: String,
    entryPrice: String,
    entryArea: String,
    entryNumberRoom: String,
    entryDescription: String,
    entryNumberAndStreet: String,
    entryNumberApartement: String,
    entryCity: String,
    entryRegion: String,
    entryPostalCode: String,
    entryCountry: String,
    entryStatus: String,
    textDateOfEntry: String,
    textDateOfSale: String,
    realEstateAgent: String?,
    lat: Double?,
    lng: Double?,
    checkedStateHopital: MutableState<Boolean>,
    checkedStateSchool: MutableState<Boolean>,
    checkedStateShops: MutableState<Boolean>,
    checkedStateParks: MutableState<Boolean>,
    listPhotoWithText: List<PhotoWithTextFirebase>?,
    itemRealEstate: RealEstateDatabase
): Response<Boolean> {
    return try {
        Response.Loading

        val rEcollection = firebaseFirestore.collection("real_estates")

        if(entryType != itemRealEstate.type ){
            rEcollection.document(id).update("type",entryType)
        }
        
        realEstateDao.updateRealEstate(entryType,id)

        Response.Success(true)
    }catch (e: Exception) {
        Response.Failure(e)
    }
}

I repeat that I want to update the rows of the table on the condition that the variable given by the method of my repo is different from the row in question

@Query("UPDATE  RealEstateDatabase SET "  
        "type = (CASE WHEN type NOT LIKE :entryType THEN (:entryType) ELSE type END) ,"  
        "price = (CASE WHEN price NOT LIKE :entryPrice THEN (:entryPrice) ELSE price END) WHERE id =:id")
suspend fun updateRealEstate(
    entryType: String,
    id: String,
    entryPrice: Int
)

this solution works but forces me to update the value anyway

CodePudding user response：

What you can do is add a condition in the WHERE clause which will prevent the execution of the UPDATE statement if both columns do not need to be updated:

@Query("UPDATE  RealEstateDatabase SET "  
       "type = CASE WHEN type NOT LIKE :entryType THEN (:entryType) ELSE type END,"  
       "price = CASE WHEN price NOT LIKE :entryPrice THEN (:entryPrice) ELSE price END "  
       "WHERE id = :id AND (type NOT LIKE :entryType OR price NOT LIKE :entryPrice)")