Update some specific field of an entity in android Room

According to SQLite Update Docs :

<!-- language: lang-java -->
@Query("UPDATE tableName SET 
    field1 = :value1,
    field2 = :value2, 
    ...
    //some more fields to update
    ...
    field_N= :value_N
    WHERE id = :id)

int updateTour(long id, 
               Type value1, 
               Type value2, 
               ... ,
               // some more values here
               ... ,
               Type value_N);

Example:

Entity:

@Entity(tableName = "orders")
public class Order {

@NonNull
@PrimaryKey
@ColumnInfo(name = "order_id")
private int id;

@ColumnInfo(name = "order_title")
private String title;

@ColumnInfo(name = "order_amount")
private Float amount;

@ColumnInfo(name = "order_price")
private Float price;

@ColumnInfo(name = "order_desc")
private String description;

// ... methods, getters, setters
}

Dao:

@Dao
public interface OrderDao {

@Query("SELECT * FROM orders")
List<Order> getOrderList();

@Query("SELECT * FROM orders")
LiveData<List<Order>> getOrderLiveList();

@Query("SELECT * FROM orders WHERE order_id =:orderId")
LiveData<Order> getLiveOrderById(int orderId);

/**
* Updating only price
* By order id
*/
@Query("UPDATE orders SET order_price=:price WHERE order_id = :id")
void update(Float price, int id);

/**
* Updating only amount and price
* By order id
*/
@Query("UPDATE orders SET order_amount = :amount, price = :price WHERE order_id =:id")
void update(Float amount, Float price, int id);

/**
* Updating only title and description
* By order id
*/
@Query("UPDATE orders SET order_desc = :description, order_title= :title WHERE order_id =:id")
void update(String description, String title, int id);

@Update
void update(Order order);

@Delete
void delete(Order order);

@Insert(onConflict = REPLACE)
void insert(Order order);
}

I want to know how can I update some field(not all) like method 1 where id = 1

Use @Query, as you did in Method 2.

is too long query in my case because I have many field in my entity

Then have smaller entities. Or, do not update fields individually, but instead have more coarse-grained interactions with the database.

IOW, there is nothing in Room itself that will do what you seek.

UPDATE 2020-09-15: Room now has partial entity support, which can help with this scenario. See this answer for more.


As of Room 2.2.0 released October 2019, you can specify a Target Entity for updates. Then if the update parameter is different, Room will only update the partial entity columns. An example for the OP question will show this a bit more clearly.

@Update(entity = Tour::class)
fun update(obj: TourUpdate)

@Entity
public class Tour {
    @ColumnInfo(name = "id")
    public long id;
    @ColumnInfo(name = "note")
    public String note;
    @ColumnInfo(name = "endAddress")
    public String endAddress;
}

public class TourUpdate {
    public long id;
    public String note;
}

Notice you have to a create a new partial entity called TourUpdate, along with your real Tour entity in the question. Now when you call update with a TourUpdate object, it will update endAddress and leave the startAddress value the same. This works perfect for me for my usecase of an insertOrUpdate method in my DAO that updates the DB with new remote values from the API but leaves the local app data in the table alone.


You could try this, but performance may be worse a little:

@Dao
public abstract class TourDao {

    @Query("SELECT * FROM Tour WHERE id == :id")
    public abstract Tour getTour(int id);

    @Update
    public abstract int updateTour(Tour tour);

    public void updateTour(int id, String end_address) {
        Tour tour = getTour(id);
        tour.end_address = end_address;
        updateTour(tour);
    }
}

I think you don't need to update only some specific field. Just update whole data.

@Update query

It is a given query basically. No need to make some new query.

@Dao
interface MemoDao {

    @Insert
    suspend fun insert(memo: Memo)

    @Delete
    suspend fun delete(memo: Memo)

    @Update
    suspend fun update(memo: Memo)
}

Memo.class

@Entity
data class Memo (
    @PrimaryKey(autoGenerate = true) val id: Int,
    @ColumnInfo(name = "title") val title: String?,
    @ColumnInfo(name = "content") val content: String?,
    @ColumnInfo(name = "photo") val photo: List<ByteArray>?
)

Only thing you need to know is 'id'. For instance, if you want to update only 'title', you can reuse 'content' and 'photo' from already inserted data. In real code, use like this

val memo = Memo(id, title, content, byteArrayList)
memoViewModel.update(memo)