Update query join - 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 2005 Forums

Transact-SQL (2005)

Update query join

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-12-28 : 16:21:20

I have the following two tables:
Table Name: Table_DM
DMID int Unchecked
ProgID int Checked
ProjID int Checked
ContractID int Checked

****************************************

Table Name: Table_Revisions
RevID int Unchecked
DMID int Checked
RevNo real Checked
Deleted bit Checked
*****************************************

Update Table_Revisions set deleted='true' where Table_DM.DMID=Table_revisions.DMID and table_DM.progid=20 and Table_DM.Projid=20 and Table_DM.ContractID=67

i am confused how to make a join condition between two table, the common field field between both the tables are DMID.

Thank you very much for the help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 16:35:31

[code]
--first look at the rows that the update statement will affect
select tr.*
from Table_Revisions tr
inner join Table_DM tdm
on tdm.DMID = tr.DMID
--and tdm.projid = tr.projid
where tdm.Projid=20
and tdm.ContractID=67

--if the select looks good then go for the update
update tr set
tr.deleted = 'true'
from Table_Revisions tr
inner join Table_DM tdm
on tdm.DMID = tr.DMID
--and tdm.projid = tr.projid
where tdm.Projid=20
and tdm.ContractID=67
[/code]

EDIT:
your double mention of projid=20 confused me

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources