Update query for db1.table1 where condition db2.ta

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 2005 Forums

Transact-SQL (2005)

Update query for db1.table1 where condition db2.ta

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-06-23 : 16:57:19

I want to run an update on db1.table1, 32 rows. change the type to GL'

recently i ran an update statement and it changed the 32 rows to another type. now i have no clue which 32 rows.

I have the same database copy(DB2) from there want to get the labelkey where type= 'GL'

select labelkey from db2.table where type='FND'

i will get 32 labelkeys.

Now want to update table1 using the above labelkey's.
loop all 32 rows labelkeys

update table1 set type='GL' where labelkey=table2.labelkey

Is it possible.

Thank you very much for the help.

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-23 : 19:59:41

Run the select statement first to be sure that is the resultset you wish to change. Then, you can see below how to write it as update statement.
(Im assuming LabelKey is pk of the table)


select *
from Database1.dbo.Table d1
inner
join Database2.dbo.Table d2 on
	d1.LabelKey = d2.LabelKey
where d1.Type <> d2.Type


--update d1
--set Type = d2.Type
--from Database1.dbo.Table d1
--inner
--join Database2.dbo.Table d2 on
--	d1.LabelKey = d2.LabelKey
--where d1.Type <> d2.Type