Erlang : Mnesia : Updating a single field value in a row

Question

I have an mnesia table with three fields, i, a and b, created using the record

-record(rec, {i, a,b}).

Now I insert a row into the table as:

mnesia:transaction( fun() -> mnesia:write("T", #rec{i=1, a=2, b=3}, write) end ).

Now what do I do if I want to update this row, and change only the value of a to 10, while leaving i and b with the same values? Is there any SQL equivalent like "UPDATE T SET a=10 WHERE i=1"?

If I do something like this:

mnesia:transaction( fun() -> mnesia:write("T", #rec{i=1, a=10}, write) end )

The row is stored as:

{rec,1,10,undefined}

Answer 1

I believe you need to read the "row", update whatever field you need, and then write back the result and all of these operations within a "transaction".

Answer 2

The value of this function will update a if used in a mnesia:transaction

update_a(Tab, Key, Value) ->
  fun() ->
    [P] = mnesia:wread({Tab, Key}),
    mnesia:write(P#pixel{a=Value})
  end.

Suggestion: have a peek at QLC if you want some syntax sugar that is more like the SQL syntax.

The performance is of course best benchmarked, but QLC has overhead, I'm not sure they are relevant compared to the other details. I just figured that the SQL example you gave would update all records that have i=1. Using QLC to extract that set of records is prettier than mnesia calls.

Also to notice, wread claims a write lock on the record directly, because we know ahead of time that we will update that record. That's a micro-optimization to avoid first a read lock, then change our mind and get a write lock. I haven't benchmarked that in a long time though.

If performance is still an issue you should look at various approaches where you use dirty operations. But you really should try to figure out how many transactions per second you need, to be 'fast enough'.