Updating a table joining another table

Question

Refresh the table joining another 1 table.

UPDATE t1 SET t1.col1 =1 FROM table1 t1 JOIN table2 t2 ON t1.ID=t2.ID WHERE t1.Name='Test' AND t2.Age=25; 

I get this error, you have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to "FROM table1 t1 JOIN table2 t2 ...

Any thoughts?

Thanks.

Answer 1

UPDATE should not have a FROM , and the SET clause should correspond to the full set of table references:

 UPDATE table1 t1 JOIN table2 t2 ON t1.ID = t2.ID SET t1.col1 = 1 WHERE t1.Name = 'Test' AND t2.Age = 25; 

Test case:

 CREATE TABLE table1 (id int, col1 int, name varchar(20)); CREATE TABLE table2 (id int, age int); INSERT INTO table1 VALUES (1, 0, 'Test'); INSERT INTO table1 VALUES (2, 0, 'Test'); INSERT INTO table1 VALUES (3, 0, 'No Test'); INSERT INTO table2 VALUES (1, 20); INSERT INTO table2 VALUES (2, 25); INSERT INTO table2 VALUES (3, 25); 

Result:

 SELECT * FROM table1; +------+------+---------+ | id | col1 | name | +------+------+---------+ | 1 | 0 | Test | | 2 | 1 | Test | | 3 | 0 | No Test | +------+------+---------+ 3 rows in set (0.00 sec) 

Answer 2

 UPDATE table1 SET col1 = 1 from table1 t1 JOIN table2 t2 ON t1.ID = t2.ID WHERE t1.Name = 'Test' AND t2.Age = 25; 

Answer 3

I have a problem updating. In table 1, I saved the username instead of userid. And for the username, the username was varchar (10). When the name exceeds 10, the name is saved with only 10 characters. Now, when I try to update using a connection request, it does not work in those fields that do not have an exact username in the users table. Write the gat name of the table in table1, but the name in the user field was -s missing.

 SELECT * FROM table1; +------+------+---------+ | id | col1 | created_by| +------+------+---------+ | 1 | 0 | steve jobs| | 2 | 1 | bill gat | | 3 | 0 | Jones | +------+------+---------+ 3 rows in set (0.00 sec) 

=== I decided this way

  UPDATE table1 AS tr JOIN users AS u ON u.name LIKE CONCAT('%', tr.created_by, '%') SET tr.created_by=u.id WHERE tr.created_by IS NOT NULL