List:General Discussion« Previous MessageNext Message »
From:Jeff Shapiro Date:February 20 2006 3:14pm
Subject:Re: Need Update Query Help (Urgent)
View as plain text  
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
Thread
Need Update Query Help (Urgent)Veerabhadrarao Narra20 Feb
  • Re: Need Update Query Help (Urgent)Jeff Shapiro20 Feb
  • Re: Need Update Query Help (Urgent)Peter Brawley20 Feb