How to make update status when at least one site assembly exist based on Revision Id?

I work on SQL server 2012 Query I face issue : I can't Update status when at Least one AssemblySiteId Record on temp table #rev

Matched temp table #location based on Revision Id .

as example

Expected Result is :

Revision Id Status

1900 Found

2000 Not Found

5000 Found

as Example 1900 status Will be Found because Revision Id 1900 on temp #rev equal LocRevisionId on temp #location

and AssemblySiteId on temp #rev equal locAssemblySiteId on temp #location

at least one

and status not found will be where at least one locAssemblySiteId not found on temp table #location but revision Id equal revision id and this case found on revision Id 2000

create table #rev
RevisionId int,
AssemblySiteId int,
Status nvarchar(200)
insert into #rev(RevisionId,AssemblySiteId)
create table #location
locRevisionId int,
locAssemblySiteId int
insert into #location(locRevisionId,locAssemblySiteId)

so How to make that please?

1 Like


i tried to do this .. hope this is what you are looking for ..

     , case when b.locAssemblySiteId is not null then 'Found' else 'Not Found' end   as Status
    rev a 
	   left join  
	location b 
	a.RevisionId = b.locRevisionId 
	a.AssemblySiteId = b.locAssemblySiteId



thank it solved

Sorry I am not able to understand .. Please tell the Meaning ..

What I did .. it solved your problem
You did on your own ( something else ) which solved

1 Like