Loading

How to make status update when only value site assembly exist on temp

How to make status update when only value site assembly exist on temp

ahmed elbarbary

6y696Databases & DBA

I work on SQL server 2012 Query I face issue : I can't Update status when at Least one Assembly Site

Record on temp table #rev Matched temp table #location based on Revision Id .

Expected Result is :

Revision Id Status
1900 Found
2000 Not Found
5000 Found

as Example

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

#location and Assembly Site on temp #rev equal locAssemblySiteId on temp #location at least one .

AND

Revision Id 2000 status Will be Not Found because Revision Id 2000 on temp #rev equal LocRevisionId on

temp #location and Assembly Site on temp #rev Not equal locAssemblySiteId on temp #location at least one .

create table #rev
(
RevisionId int,
AssemblySiteId int,
Status nvarchar(200)
)
insert into #rev(RevisionId,AssemblySiteId)
values
(1900,200),
(2000,300),
(5000,800)
create table #location
(
locRevisionId int,
locAssemblySiteId int
)
insert into #location(locRevisionId,locAssemblySiteId)
values
(1900,200),
(1900,150),
(2000,290),
(2000,310),
(5000,800),
(5000,820)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar6y ago
Hi
SELECT Convert(varchar(32),RevisionId)+' Mached'
FROM #rev
WHERE EXISTS
(SELECT Top 1* FROM #location WHERE RevisionId = locRevisionId and AssemblySiteId = locAssemblySiteId)
Union All
SELECT Convert(varchar(32),RevisionId)+' Not Mached'
FROM #rev
WHERE RevisionId Not In
(SELECT locRevisionId FROM #location WHERE RevisionId = locRevisionId and AssemblySiteId = locAssemblySiteId);
Try this query
0