On Sunday 12 May 2002 03:39 am, John W Higgins wrote:
First, you cannot do what you want to do - the UPDATE query does not allow you to write sql like that. You cannot use multiple tables in the UPDATE statement.
Second, good RDBMS normalization rules say that your query is flawed conceptually. You shouldn't be copying data between the tables anyways. You should be joining them as needed and using the data from each table as required.
The solution to what you want to do is a hack at best - probably some type of script that would run the select and then multiple updates with the appropriate value(s) changed.
Sorry I don't have better news for you,
John W Higgins john@wishdev.com
John and MySQL/SuSE wizards, What I want to be able to do is move data from one database table to another, and then to export to a different server. I supppose I could export to Excel or MS_Access, but I'm trying to do the operation all within MySQL. Is if I can "JOIN" the two tables, do you know the syntax that would accomplish what I'm trying to do below?
UPDATE A SET A.field2=B.field2 WHERE A.field1=B.field1
This seems like such a common need but if you could help me with a JOIN statement, possibly then I could export the data and load into a new table. TIA, Andrew Lietzow The ACL Group, Inc.