issue with updation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue with updation

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-12-07 : 21:33:39

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

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-12-07 : 22:11:12

please somebody have a check on the query....

AlDragon
Starting Member

12 Posts

Posted - 2004-12-07 : 22:50:12

quote:
Originally posted by sqllearner

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

Try this out

Declare @ref_id int --not sure what type ref id is
SET @ref_id = (Select max(ref_id) from tbl_sale_detail)

update tbl_prop_log
set ref_id = @ref_id
from tbl_prop_loga
where prop_id in (select prop_id from tbl_sale_detail)
and ref_id IS NULL

Al

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-12-07 : 23:14:41

Thanks a lot I was looking for something like this

UPDATE Table1
SET field1=(SELECT MAX(field1) FROM table2
WHERE field2=table1.field2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources