在SQLITE中更新具有独立条件的多行

Question

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

这段代码可以完美地根据条件更新表中的一行我想对每个链接到条件的几行做同样的事情

这是有问题的实体/表格

@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,
)

我还放了我的存储库的方法，这个方法有与我的表相同类型的参数来修改

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)
}
}

我重复一遍，我想更新表中的行，条件是我的回购方法给出的变量与有问题的行不同

@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
)

此解决方案有效，但无论如何都会迫使我更新值

Answer 1

您可以在WHERE子句中添加一个条件，如果两个列都不需要更新，则该条件将阻止执行UPDATE语句：

@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)")