1
$\begingroup$

I would like to update several entries in a mySQL database which I use in combination with Mathematica. But the following code does not work:

Map[ SQLUpdate[conn, "table_name","column2update", {1}, SQLColumn["selection_column"] = #" ] &, list_with_selection_values]

The column "column2update" is defined as datatype "Boolean". I would like to insert a "1" (= true) into certain entries. "selection_column" contains unique data (in that case stock symbols). I would like to us the "list_with_selection_values" to identify the entries where I change "column2update" to "1".

Does anyone know why my function does not work? All database connections are ok. Thanks!

Improve this question
$\endgroup$
5
  • $\begingroup$ The code you posted has mismatching quotes after # to begin with. $\endgroup$
    – egwene sedai
    Commented Mar 20, 2015 at 15:50
  • $\begingroup$ Yes, you are rigth but it is just a typing error here in the forum. My code is ...SQLColumn["selection_column"] = # ] &,... Nevertheless thanks for the tip $\endgroup$
    – Phadreus
    Commented Mar 20, 2015 at 17:13
  • 2
    $\begingroup$ Have you tried using SQLExecute? I find it easier to use because you can just do straight sql commands. I have also used it with Map as you are trying to do here. $\endgroup$
    – c186282
    Commented Mar 21, 2015 at 1:54
  • $\begingroup$ Yes I tried already SQL Execute: SQLExecute[tradDBconn, "UPDATE index_master SET index_master_historical_data =0 WHERE \ FIND_IN_SET(index_master_symbol,('^BTS,^CSO'))"] but I do not know how to create a list in Mathematica the will be look like ('^BTS,^CSO') -> I have about 200 index symbols I would like ti check. $\endgroup$
    – Phadreus
    Commented Mar 21, 2015 at 16:51
  • 1
    $\begingroup$ @Phadreus what's the purpose of the Map? Unless MMA is cleverer than I think it is with SQL its going to be far more efficient to use MMA to generate an IN or a set of OR clauses or even a set of Update statements that you can SQLExecute as one Transaction. I'm pretty sure doing it the way you currently are will generate round trip IO for each value in the list rather than one round trip during which the updates are executed. $\endgroup$
    – Gordon Coale
    Commented Mar 21, 2015 at 18:47

1 Answer 1

Reset to default
1
$\begingroup$

Thanks to all of you. I solved the problem. I missed the {} brackets around the database column (I thought I do not need them, if I specify just one column).

Here the final code for later questions:

Map[
  SQLUpdate[dbconnection, "database",
    {"column2update"}, {value to be set}, 
    SQLColumn["selection_column"] == # ] &,
 list_with_selection_values]
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.