CODEWITHSUNDEEP
phpmysqlsqldatabase
ShanjayG
ShanjayG

Reputation: 1023

MySQL, Update query with inner joins fails silently

I have a MySQL query update with inner joins is not working while similar select query with inner joins works perfectly.

Working SELECT query

SELECT fa.tax_rate, efa.entity_id
FROM water_civicrmv2.civicrm_financial_account as fa
    INNER JOIN water_civicrmv2.civicrm_entity_financial_account efa ON efa.financial_account_id = fa.id 
    INNER JOIN water_civicrmv2.civicrm_option_value cov ON cov.value = efa.account_relationship
    INNER JOIN water_civicrmv2.civicrm_option_group cog ON cog.id = cov.option_group_id
    WHERE efa.entity_table = 'civicrm_financial_type'
    AND cov.name = 'Sales Tax Account is'
    AND cog.name = 'account_relationship'
    AND fa.is_active = 1

Failed UPDATE query 

 UPDATE water_civicrmv2.civicrm_financial_account as fa
    INNER JOIN water_civicrmv2.civicrm_entity_financial_account efa ON efa.financial_account_id = fa.id 
    INNER JOIN water_civicrmv2.civicrm_option_value cov ON cov.value = efa.account_relationship
    INNER JOIN water_civicrmv2.civicrm_option_group cog ON cog.id = cov.option_group_id
    SET fa.tax_rate = '123123' 
    WHERE efa.entity_table = 'civicrm_financial_type'
    AND cov.name = 'Sales Tax Account is'
    AND cog.name = 'account_relationship'
    AND fa.is_active = 1

I really don't know why it fails silently as there is no syntax error, just nothing happens.

Upvotes: 0

Views: 200

Answers (1)

Phil Grigsby
Phil Grigsby

Reputation: 271

Change your update SQL so that the object of the UPDATE is only the single table water_civicrmv2.civicrm_financial_account:

 UPDATE water_civicrmv2.civicrm_financial_account as fa
    SET fa.tax_rate = '123123' 
    WHERE fa.id = 
          (SELECT fa.id
             FROM water_civicrmv2.civicrm_entity_financial_account efa
             INNER JOIN water_civicrmv2.civicrm_option_value cov ON cov.value = efa.account_relationship
             INNER JOIN water_civicrmv2.civicrm_option_group cog ON cog.id = cov.option_group_id
             WHERE efa.financial_account_id = fa.id 
               AND efa.entity_table = 'civicrm_financial_type'
               AND cov.name = 'Sales Tax Account is'
               AND cog.name = 'account_relationship'
               AND fa.is_active = 1)

Upvotes: 1

Related Questions