impossible to update from a select

Question

Hi i ve got two tables like this:

mysql> describe tb_data_iae;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id_dialecte        | int(11)      | NO   | PRI | NULL    | auto_increment | 
| nb_champs          | tinyint(4)   | NO   |     | 0       |                | 
+--------------------+--------------+------+-----+---------+----------------+

and

mysql> describe tb_dialecte;
+-------------------+--------------+------+-----+------------+----------------+
| Field             | Type         | Null | Key | Default    | Extra          |
+-------------------+--------------+------+-----+------------+----------------+
| id_dialecte       | int(11)      | NO   | PRI | NULL       | auto_increment |
| nb_champs         | tinyint(4)   | NO   |     | 0          |                | 
+-------------------+--------------+------+-----+------------+----------------+

I try to update first table "nb_champs" field from the same field coming from the second table

mysql> update tb_data_iae 
       set nb_champs=tb_dialecte.nbchamps  
      from tb_dialecte 
      where tb_dialecte.id_dialecte = tb_data_iae.id_dialecte;

ERROR 1064 (42000): 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 tb_dialecte where tb_dialecte.id_dialecte = tb_data_iae.id_dialecte' at line 1

I don't know how to debug this, as I try many queries but no ones works and the error message is pretty much the same everytime as the one above...

Thx for help !

Are you simply trying to do a 1 time clone or how will this be used? — daemonofchaos, May 26 '11 at 20:27
yes I will use this query one time, just to reorganize data in the db — krifur, May 26 '11 at 20:33

score 2 · Accepted Answer · edited May 26 '11 at 20:37

2

update tb_data_iae set nb_champs=(SELECT tb_dialecte.nbchamps 
from tb_dialecte 
where tb_dialecte.id_dialecte = tb_data_iae.id_dialecte);

Though I'd ask why store the same values in two tables?

edited May 26 '11 at 20:37

Johan

74,508
24
191
319

answered May 26 '11 at 20:27

Cfreak

19,191
6
49
60

It s because I made a migration of store data from one table to another, to sort out information more clearly in the database – krifur May 26 '11 at 20:31
there s something I don t understand in this query, how do the update know which id_dialecte as to be set with the nb_champs value ? – krifur May 26 '11 at 20:45
It looks up the record in tb_dialecte from the id you give it in the where statement. It's the same way a JOIN works in a select statement – Cfreak May 26 '11 at 21:19

score 1 · Answer 2 · edited May 26 '11 at 20:37

1

When I look in http://dev.mysql.com/doc/refman/5.0/en/update.html, it looks like update...from is not allowed.

update tb_data_iae,tb_dialecte 
set tb_data_iae.nb_champs=tb_dialecte.nbchamps 
where nb_dialecte.id_dialecte = tb_data_iae.id_dialecte;

edited May 26 '11 at 20:37

Johan

74,508
24
191
319

answered May 26 '11 at 20:29

Luuk

1,959
1
21
43

what about this thread, it s the one i get inspired to write my query but for me it s not working ? http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match – krifur May 26 '11 at 20:39
1

that question was for Microsoft SQL Server, it won't work in MySql I think – Luuk May 26 '11 at 20:59
god dammit, note for myself read more carefully the associated tags, thx ! – krifur May 26 '11 at 21:06

impossible to update from a select

2 Answers2