Syntax error while trying to run a update query in mysql

Question

While trying to execute the query given below, I get an syntax error I need to update a column value from table civicrm_address and move it from abc_abc_drupal_civi_4_17 database to abc_drupal database

In order to achieve it, I get an syntax error near from FROM, The error I get is as follows

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for
 the right syntax to use near 'FROM abc_abc_drupal_civi_4_17.civicrm_address,abc_drupal.civic' at line 3

How can I fix it?

UPDATE abc_drupal.civicrm_address 
SET abc_drupal.civicrm_address.state_province_id = abc_abc_drupal_civi_4_17.civicrm_address.state_province_id
FROM abc_abc_drupal_civi_4_17.civicrm_address,abc_drupal.civicrm_address
WHERE abc_drupal.civicrm_address.state_province_id IS NULL 
AND   abc_abc_drupal_civi_4_17.civicrm_address.state_province_id IS NOT NULL
AND   abc_abc_drupal_civi_4_17.civicrm_address.id = abc_drupal.civicrm_address.id  
AND   abc_abc_drupal_civi_4_17.civicrm_address.contact_id IS NOT NULL;

Hi @JohnConde, I have added the error , kindly suggest fixes in my query — harshal
– harshal, Commented Oct 3, 2018 at 12:09

Gordon Linoff · Accepted Answer · 2018-10-03 12:15:22Z

1

MySQL puts JOIN in the UPDATE clause, not through a separate FROM (the FROM is used by SQL Server and Postgres).

Your query as written is hard to decipher. I would strongly recommend that you use table aliases, so the query is more easily written and read:

UPDATE abc_drupal.civicrm_address a JOIN
       abc_abc_drupal_civi_4_17.civicrm_address aa
       ON aa.id = a.id
    SET a.state_province_id = aa.state_province_id
WHERE a.state_province_id IS NULL 
      aa.state_province_id IS NOT NULL AND   
      aa.contact_id IS NOT NULL;

answered Oct 3, 2018 at 12:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2018-10-03 12:21:17Z

1

You need to specify set later like below:

   UPDATE TABLEA a 
   JOIN TABLEB b ON a.join_colA = b.join_colB  
   SET a.columnToUpdate = [something]



 UPDATE abc_drupal.civicrm_address 
    join abc_abc_drupal_civi_4_17.civicrm_address inner join abc_drupal.civicrm_address
    on abc_abc_drupal_civi_4_17.civicrm_address.id = abc_drupal.civicrm_address.id  
    SET abc_drupal.civicrm_address.state_province_id = abc_abc_drupal_civi_4_17.civicrm_address.state_province_id
where abc_drupal.civicrm_address.state_province_id IS NULL 
    AND  abc_abc_drupal_civi_4_17.civicrm_address.state_province_id IS NOT NULL
    AND  abc_abc_drupal_civi_4_17.civicrm_address.contact_id IS NOT NULL

edited Oct 3, 2018 at 12:21

answered Oct 3, 2018 at 12:09

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

2 Comments

harshal Over a year ago

Hi @fa06 , it gives an error - unexpected ordering of clauses (near where)

Fahmi Over a year ago

modified my answer, you can check,, what is relation key between abc_drupal.civicrm_address & abc_abc_drupal_civi_4_17.civicrm_address table

Collectives™ on Stack Overflow

Syntax error while trying to run a update query in mysql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related