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!

  • $\begingroup$ The code you posted has mismatching quotes after # to begin with. $\endgroup$ Mar 20 '15 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
    Mar 20 '15 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
    Mar 21 '15 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
    Mar 21 '15 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$ Mar 21 '15 at 18:47

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:

  SQLUpdate[dbconnection, "database",
    {"column2update"}, {value to be set}, 
    SQLColumn["selection_column"] == # ] &,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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