0

I'm attempting to use the following code to JOIN my MySQL tables, TableA and TableB, when both their owner and pos columns match, setting TableA's val in the matching record to TableB's val:

UPDATE TableA A 
JOIN TableB B
ON A.owner = B.owner AND A.pos = B.pos
SET A.val = B.val

TableA has around 100,000 records and TableB has around 10,000 and there should only be one owner/pos match in each table.

When I execute this query everything just hangs and I end up having to abort the execution. Is there something I'm missing syntactically or otherwise to get my desired result?

Edit:

I have also tried the following; the results were the same:

UPDATE TableA 
JOIN TableB
ON TableA.owner = TableB.owner AND TableA.pos = TableB.pos
SET TableA.val = TableB.val
amoeba
  • 95
  • 4
  • 17
  • prepend `EXPLAIN` to the query and see what MySQL has to say. Post results here :) – Andy Jones May 09 '15 at 18:43
  • With `EXPLAIN` the output is: 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 'UPDATE TableA A JOIN TableB B ON A.owner = B.owner' at line 2 – amoeba May 09 '15 at 18:49
  • Can you select the join? `SELECT * FROM TableA A INNER JOIN TableB B ON TableA.owner = TableB.owner AND TableA.pos = TableB.pos` – grepsedawk May 09 '15 at 19:19
  • Also, can we see the Schema? – grepsedawk May 09 '15 at 19:21
  • Yes, I am able to select from the above `JOIN`. Both tables have `INT`s for their owner and pos columns and `TINYINT`s for their val column. – amoeba May 09 '15 at 19:42

2 Answers2

0

Replace A.owner = B.owner AND A.pos = B.pos with (A.owner = B.owner AND A.pos = B.pos) and see if Explain plan gives any error?

Ashish Yete
  • 51
  • 1
  • 7
  • `EXPLAIN` gives the error: 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 'UPDATE TableA A JOIN TableB B ON (A.owner = B.owner ' at line 2 – amoeba May 09 '15 at 19:05
  • you can refer this link, http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql It should resolve your issue. – Ashish Yete May 09 '15 at 19:10
  • I added `INNER JOIN` but that didn't solve it. That was the only difference between my code and that answer's code that I could see. – amoeba May 09 '15 at 19:16
0

Below query may help:

UPDATE A
SET A.val = B.val
from TableA A
INNER JOIN TableB B
ON A.owner = B.owner AND A.pos = B.pos
Rajesh
  • 2,135
  • 1
  • 12
  • 14
  • That shouldn't make a difference: http://stackoverflow.com/questions/15209414/mysql-update-join The `SET` is after the join there. – grepsedawk May 09 '15 at 19:21