MySQL and Matlab - Replacing a whole column of data
1 次查看(过去 30 天)
显示 更早的评论
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.
0 个评论
回答(1 个)
Darik
2013-1-21
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)
3 个评论
Darik
2013-1-22
编辑:Darik
2013-1-22
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)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!