On Monday 20 February 2006 03:27, Veerabhadrarao Narra wrote: > Hi > > i ahve one table table_1 and columns like col_1,col_2,col_3 > > col_1 col_2 col_3 > 1 aa aaa > 2 bb > > Now i want to update my table table_1 SET col_3 as bbb where max of col_1 > > I wrote this below Query but it shows error how to write > > UPDATE table_1 > SET col_3 = 'bbb' > WHERE col_1 = (SELECT max(col_1) FROM table_1)
It appears that you can't do what you want. This is at the bottom of the UPDATE syntax page: " Currently, you cannot update a table and select from the same table in a subquery." <http://dev.mysql.com/doc/refman/5.0/en/update.html> However, you can to something like: select @maximum_column :=max(col_1) from table_1; UPDATE table_1 SET col_3 = 'bbb' WHERE col_1 = @maximum_column; Perhaps someone else has a better solution. -- Jeff Shapiro listserv only address -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]