Laercio Xisto Braga Cavalcanti wrote:
Hi all,
I'm using mysql 3.23.54 under RedHat9 trying to update a field named pac_fone in a table named paciente with the value of a field named ita_fone from a table named italica as follows:
update paciente SET pac_fone = (select ita_fone from italica i, paciente p where i.ita_matricula = p.pac_matricula and i.ita_dv = p.pac_dv);
and got the following error:
mysql> update paciente -> SET pac_fone = (select ita_fone -> from italica i, paciente p -> where i.ita_matricula = p.pac_matricula -> and i.ita_dv = p.pac_dv); ERROR 1064: You have an error in your SQL syntax near 'select ita_fone from italica i, paciente p wh' at line 2
Can anybody help me with it?
Regards,
Laercio.
Subselects require mysql 4.1.0 or higher. You could rewrite this as a multiple-table update starting with mysql 4.0.4.
With 3.23.54, this will be more difficult. I suppose you could use CREATE...SELECT to make a new table with all the columns from paciente except pac_fone, plus the one column from italica:
CREATE TABLE newtable SELECT p.id, p.pac_dv,... i.ita_fone FROM italica i, paciente p WHERE i.ita_matricula = p.pac_matricula AND i.ita_dv = p.pac_dv;
and then replace paciente with the new table:
RENAME TABLE paciente TO backup, newtable TO paciente;
You'd have to recreate any indexes on paciente after that.
Perhaps someone will suggest a better way.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
