Hi Michael, Thank you very much. It works good.
Regards, Laercio. > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
