1

I am trying to Update a column in a table in one database after doing two inner joins, one of which is with a table from another database on the same server. I was trying to follow this solution here: Update Query with INNER JOIN between tables in 2 different databases on 1 server

This isn't working for me. It gives me: you have an error in your syntax near s_u

Here's my attempt (UPDATE: I removed the code sanitation. So this is the exact code I'm running now):

UPDATE s_u
  SET s_u.bill_address_id=spree_billing.id
  FROM spree_users AS s_u
  INNER JOIN magento.customer_entity_int AS default_billing
    ON default_billing.entity_id=s_u.magento_id
    AND default_billing.attribute_id=14
  INNER JOIN spree_addresses AS spree_billing
    ON spree_billing.magento_address_id=default_billing.value;
Community
  • 1
  • 1
Steven Harlow
  • 631
  • 2
  • 11
  • 26
  • 1
    The link you provided has the INNER JOIN before the SET. Also "default_billing.attribute_id=14" should be on the where clause. Sorry if I'm wrong, not to much mysql exp. – Esselans Nov 21 '13 at 00:04
  • The answer to the question in the link has the SET before the INNER JOIN and no WHERE clause (I'm assuming because that's taken care of by joining) – Steven Harlow Nov 21 '13 at 00:09
  • The code in the question has the join after the set, where it belongs. Also, having the filter in the join clause instead of the where clause is ok. – Dan Bracuk Nov 21 '13 at 00:09
  • The error message says that the error is near s_u. Maybe MySQL does not allow alias names in update queries. Try it with the actual table name. – Dan Bracuk Nov 21 '13 at 00:13
  • Tried your suggestion @DanBracuk, but it's still there in the same spot. – Steven Harlow Nov 21 '13 at 00:17
  • error message after removing aliases: 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 spree_users INNER JOIN magento.customer_entity_int ON customer_entity' at line 3 – Steven Harlow Nov 21 '13 at 00:18
  • That looks like you failed to qualify customer_entity with the database name. – Dan Bracuk Nov 21 '13 at 00:21
  • @DanBracuk I think I only need to qualify the database name after the INNER JOIN. I tried anyway and still same: 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 spree_users INNER JOIN magento.customer_entity_int ON magento.custome' at line 3 – Steven Harlow Nov 21 '13 at 01:07

2 Answers2

1

You reference to something called spree_billing which does not appear anywhere in your statement. You need to fix it. The problem is probably here:

UPDATE s_u
  SET s_u.bill_address_id=billing.id  -- modify to billing, as it is what third_table's alias is called
  FROM some_table AS s_u
  INNER JOIN magento.another_table AS default_billing
    ON default_billing.entity_id=s_u.magento_id
    AND default_billing.attribute_id=14
  INNER JOIN third_table AS billing
    ON billing.magento_address_id=default_billing.value;
cha
  • 10,301
  • 1
  • 18
  • 26
  • Sorry, that was my attempt to sanitize the code. I missed that spree_billing reference. In my actual code all the tables and the references match up. I updated the code – Steven Harlow Nov 21 '13 at 00:11
0

please can you try this :

UPDATE spree_users AS s_u
 INNER JOIN magento.customer_entity_int AS default_billing
   ON default_billing.entity_id=s_u.magento_id
   AND default_billing.attribute_id=14
 INNER JOIN spree_addresses AS spree_billing
   ON spree_billing.magento_address_id=default_billing.value
SET s_u.bill_address_id=spree_billing.id;

I didn't tried your tables but test on my sample test

The answer in you answer is for SQL-Server. you should look this link. UPDATE multiple tables in MySQL using LEFT JOIN

updated

so, syntax error is removed but it takes long. I want to check how many records matches JOIN or join is performing efficiently. please can you post following query's output?

SELECT COUNT(*)
FROM spree_users AS s_u
 INNER JOIN magento.customer_entity_int AS default_billing
   ON default_billing.entity_id=s_u.magento_id
   AND default_billing.attribute_id=14
 INNER JOIN spree_addresses AS spree_billing
   ON spree_billing.magento_address_id=default_billing.value
Community
  • 1
  • 1
Jason Heo
  • 9,956
  • 2
  • 36
  • 64
  • It may contain error. disable commit commit before run query. – Jason Heo Nov 21 '13 at 00:34
  • So this one looks like it's trying to run, but it's running infinitely and doesn't update the table at all. (I took out that first semicolon too of course) – Steven Harlow Nov 21 '13 at 00:40
  • @StevenHarlow sorry, first semicolon was typo. I updated my answer. – Jason Heo Nov 21 '13 at 00:51
  • I tried the SELECT COUNT(*) statement and it's also running for a long time (10 minutes so far). I tried a SELECT * statement with and without a limit 10 included and it ran with the limit but is going for a long time when not included. Anyway obvious way to optimize this? – Steven Harlow Nov 21 '13 at 01:24
  • @StevenHarlow yes, there is. but more information is needed. post another question about your SELECT performance. it must include table creation statement, and how many records in each table. and output of `EXPLAIN SELECT`. then many people can help you. – Jason Heo Nov 21 '13 at 01:33
  • Thanks. I'll do that – Steven Harlow Nov 21 '13 at 02:07