Bug #1067 Using Update to grab a field from a second table to update main table
Submitted: 17 Aug 2003 8:08 Modified: 21 Aug 2003 4:31
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:MySQL 4.0 MAX OS:Windows (Windows 2000 Server)
Assigned to: CPU Architecture:Any

[17 Aug 2003 8:08] [ name withheld ] 
Description:
Is there or could there be a way to do an update from a select?  For example, I have two tables with a PostalCode field, and each table is tied together by a UserID.  If I could say update Table2 with Table 1 set Table2.PostalCode =Table1.PostalCode where Table2.UserID = Tabel1.UserID, that would be really cool.  As it is now, I have to write a PHP script to accomplish this task.  If there is already way, all the better.

How to repeat:
Is there or could there be a way to do an update from a select?  For example, I have two tables with a PostalCode field, and each table is tied together by a UserID.  If I could say update Table2 with Table 1 set Table2.PostalCode =Table1.PostalCode where Table2.UserID = Tabel1.UserID, that would be really cool.  As it is now, I have to write a PHP script to accomplish this task.  If there is already way, all the better.

Suggested fix:
Is there or could there be a way to do an update from a select?  For example, I have two tables with a PostalCode field, and each table is tied together by a UserID.  If I could say update Table2 with Table 1 set Table2.PostalCode =Table1.PostalCode where Table2.UserID = Tabel1.UserID, that would be really cool.  As it is now, I have to write a PHP script to accomplish this task.  If there is already way, all the better.
[21 Aug 2003 4:31] Peter Zaitsev 
Joshua,

Please take a look at multi table updates functionality,

Also in MySQL 4.1 you can have subselects in UPDATE statement which allow to solve most of such problems.
[23 May 2010 17:12] stephan olson 
I can not start MySQL server from Window Services.  Every time I start MySQL an error message 1067 appears.  What can be done to start MySQL server without the error message?