UPDATE Query Giving me an SQL Error (1064)

Question

I am trying to update a table in my database from another table. Here is my Syntax, but I can't seem to find any issues with it. I keep getting SQL Error (1064).

UPDATE customers b
SET customers.takerid = customer_update_2016.ot
FROM customer_update_2016 a, customers b
WHERE a.phone = b.phone && a.lname = b.lname

SQL Error (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 customer_update_2016 a, customers b WHERE a.phone = b.phone & a.lname =b' at line 3

Solution:

UPDATE customers
INNER JOIN customer_update_2016
ON customers.phone = customer_update_2016.phone
AND customers.lname = customer_update_2016.lname 
SET customers.takerid = customer_update_2016.ot

I think you're missing a `JOIN` to use multiple tables in the query like this. Example: http://stackoverflow.com/questions/982919/sql-update-query-using-joins — d-_-b, Feb 14 '16 at 19:04
Possible duplicate of [MYSQL Update Statement Inner Join Tables](http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables) — paparazzo, Feb 14 '16 at 19:35
So does this really worked? I mean the part that says Solution? or was that the solution you tried to solve your problem? — Ricardo Rivera Nieves, Apr 08 '20 at 00:56

score 0 · Answer 1 · answered Feb 14 '16 at 19:05

0

you have both mysql and sql-server
which one?

UPDATE customers
   SET customers.takerid = customer_update_2016.ot 
  FROM customers
  JOIN customer_update_2016
         on customers.phone =    customer_update_2016.phone 
        and customers.lname =    customer_update_2016.lname 
        and customers.takerid <> customer_update_2016.ot

answered Feb 14 '16 at 19:05

paparazzo

44,497
23
105
176

When I run this query I get: SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to you MySQL server version for the right syntax to use near ' FROM customers JOIN customer_update_2016 on customers.phone = cu' at line 3 – Patrick Griffith Feb 14 '16 at 19:30
Pretty sure this works with sql-server. You had it tagged both ways. – paparazzo Feb 14 '16 at 19:33

score 0 · Answer 2 · answered Feb 14 '16 at 19:28

0

Follow something like this always

UPDATE [table1_name] AS t1 
INNER JOIN [table2_name] AS t2 
ON t1.[column1_name] = t2.[column1_name] 
SET t1.[column2_name] = t2.[column2_name];

answered Feb 14 '16 at 19:28

kidz

308
1
8

Ricardo Rivera Nieves · Answer 3 · 2020-04-08T23:18:48.770

To solve your problem you should use JOIN, in my case I had to pass data from one table to another like you and this was the way that solved my problem, hope it help with yours and others.(I'm using MariaDB v10.4)

UPDATE 
table1
LEFT JOIN table2 AS tb2
ON tb2.fieldThatJoin = table1.fieldThatJoin//this part indicate the field that join them
SET 
table1.field1 = tb2.field1;
//if you want to update more than one field then do this
table1.field1 = tb2.field1,
table1.field2 = tb2.field2,
table1.field3 = tb2.field3;//remember to put the ';' at the end

UPDATE Query Giving me an SQL Error (1064)

3 Answers3