I am trying to use the mysql_query statement to update records in my database.

Can anyone understand why this code would not work and how I would manage to add dynamically the key values to the UPDATE statements ?

...


{
    "diff_records": {
        "app_portfolio_manager": "New Manager"
    }
}


- name: case 1b insert app_dict to db table app_info
  community.mysql.mysql_query:
      - UPDATE app_info
        SET ( {{ diff_records.keys() | join(', ') }} ) VALUES ( {{ diff_records.values() | map('regex_replace', '^(.*)$', "'\1'") | join(', ') }} )
        WHERE app_name = '{{ app_dict.app_name }}' 
        

fatal: [localhost]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "query": [
                "UPDATE app_info SET ( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new App'"
            ],
            "single_transaction": true
        }
    },
    "msg": "Cannot execute SQL 'UPDATE app_info SET ( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new App'' args [None]: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( app_portfolio_manager ) VALUES ( 'New Manager' ) WHERE app_name = 'My new...' at line 1\")"
}

CodePudding user response：

The following only works with single key value variable no go for multiple - name: case 1b insert app_dict to db table app_info community.mysql.mysql_query: - UPDATE app_info SET {{ diff_records.keys() | join(', ') }} = {{ diff_records.values() | map('regex_replace', '^(.*)$', "'\1'") | join(', ') }} WHERE app_name = '{{ app_dict.app_name }}'

CodePudding user response：

update . Adding a simple dict loop seems to have done the trick.

- name: case 1b insert app_dict to db table app_info 
  community.mysql.mysql_query:  
    - UPDATE app_info SET  {{ item.key }} '{{ item.value }}'   
      WHERE app_name = '{{ app_dict.app_name }}' 
  loop: "{{ diff_records | dict2items }}"