MySQL and Matlab - Replacing a whole column of data

I'm working on a program that takes in some data from a database in MySQL, changes some numbers, and then overwrites the old data on MySQL with the new stuff. (Specifically, the data I'm taking in is output from a weather forecasting model). What I'm struggling with is being able to replace the old data in the database with the edited data.

In my program, the new data (solar radiation values) is a column of numbers in the 3rd column of the matrix WxData (so it can be accessed with WxData(:,3)).

In the MySQL database, the values I want to change are under the column titled "radiation" in the table "wrf". "dbConn" is the name of the database connection.

I tried something like

if true
     update(dbConn, 'wrf', {'radiation'}, WxData(:,3), 'WHERE radiation > -1')
end

The update function in the database toolbox in Matlab requires a where clause input so I just put something that is always true. But this method doesn't seem to work...it ends up changing every single radiation value in the database table to the same number (possibly the value at WxData(1,3)).

I've tried a couple other ways but nothing worked. How can I just replace the whole column of radiation values in the database with a new column? Seems like it should be simple.

Answer 1

[Darik]

See example 3 in the UPDATE documentation, "Example 3 — Update Multiple Records with Different Constraints".

Basically you a need a different where condition for each row in your table. You'll need a primary key column which uniquely specifies each row. Assuming you have a primary key in the table wrf named wrf_id, and it's the first column in WxData, it'll look something like:

pk_value = WxData(:,1);

%generate a cell array of where clauses
where = arrayfun(@(id) sprintf('WHERE wrf_id = %d', id), pk_value, 'UniformOutput', false); 
update(dbConn, 'wrf', {'radiation'}, WxData(:,3), where)

[Kyle Chudler]

 Unfortunately my table doesn't have a unique identifier for each row. There is a column which specifies location, and a column that specifies output time...it looks something like this:

if true
  Location|Time
  1       | 12:00
  1       | 13:00
  1       | 14:00
  2       | 12:00
  2       | 13:00
  2       | 14:00

end

So there is a set of times for each location number. I could uniquely identify each row if I could use two variables though (only one row exists for a given location and time). These are in WxData(:,1) and WxData(:,2) respectively. Is it possible to modify the code above to work for two row identification variables?

[Darik]

Yeah that's called a composite primary key, at least if you can absolutely guarantee you'll never have the same (location, time) pair listed twice. For the future you may want to explicitly define (Location, Time) as the primary key in MySql, or add in an autonumber PK field. (If the table is really large, this will also have the advantage of making the update operation faster, since MySql will be able to use indexes to locate rows to update)

If your time data is numeric, and WxData is also a numeric array

where = arrayfun(@(loc, t) sprintf('WHERE Location = %d AND Time = %d', loc, t), WxData(:,1), WxData(:,2), 'UniformOutput', false)

Otherwise if time is a string, and WxData is a cell array

where = cellfun(@(loc, t) sprintf('WHERE Location = %d AND Time = "%s"', loc, t), WxData(:,1), WxData(:,2), 'UniformOutput', false)

[Kyle Chudler]

I get the following error when I try to use the code you gave me for time being a string

Error using arrayfun All of the input arguments must be of the same size and shape. Previous inputs had size 1 in dimension 2. Input #3 has size 19.

I think it has something to do with the time array being a x by 19 char array.