issue with the update 1

[SqlHunter]

I want to select a max(prop) id from table1 and update it in table2
something like this

update tbl_prop_log
set a.ref_id=max(b.ref_id)
from tbl_prop_loga ,tbl_sale_detail b
where a.prop_id in(select prop_id from tbl_sale_detail)and a.ref_id IS NULL

This gives an error..please help me with this query

 

[donutman]

Tells us what you want to do in English. And define your table structure. Normally, it would be helpful to know what the error message is, but in this case mums OK.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SqlHunter]

I want to update field1 of table1 with the max value of field1 of table2 where field2 of table1 and field 2 of table2 are equal

 

[donutman]

That actually helped, but I'm curious why you have

and a.ref_id IS NULL

in your version. Here's a solution to your question, but before you use it, a warning. Doing an Update on a table can be very dangerous. This update will effect every row of the table. Is that what you want? What will happen if it results in trashing field1 of Table1? I'm not confident that you really want to do what you've asked, are you?

[Blue]UPDATE[/Blue] Table1
   [Blue]SET[/Blue] field1[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]field1[Gray])[/Gray] [Blue]FROM[/Blue] table2 
                  [Blue]WHERE[/Blue] field2[Gray]=[/Gray]table1.field2[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SqlHunter]

Thanks a lot