July 30, 2020 at 3:05 am
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)
July 30, 2020 at 2:35 pm
What have you tried so far?
I can think of a few ways to do this. A LEFT JOIN onto table #location where the values match is the approach I'd take. Then a CASE statement for when the locRevisionId IS NULL then 'Not Found' ELSE 'Found'. You only need 2 columns, the RevisionId and the CASE statement one and you are done, right?
Alternately, you could make another table with 2 new bit columns that default to 0, insert the values from #rev into it, then update the first bit column where RevisionID IN (SELECT locRevisionID from #location) and then a second update on the second bit column where AssemblySiteId IN (SELECT locAssemblySiteID). Finally, you do a CASE ON bit1+bit2 = 2 THEN 'Found' ELSE 'Not Found'.
The advantage of method 1 is it is a single lookup and a join and quick and easy to maintain. The advantage of number 2 is you have additional data points which may be useful later such as if you wanted to indicate a partial match.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 30, 2020 at 5:43 pm
drop table if exists #rev;
go
create table #rev(
RevisionId int,
AssemblySiteId int,
Status nvarchar(200));
go
insert into #rev(RevisionId,AssemblySiteId)
values
(1900,200),
(2000,300),
(5000,800);
drop table if exists #location;
go
create table #location(
locRevisionId int,
locAssemblySiteId int);
go
insert into #location(locRevisionId,locAssemblySiteId)
values
(1900,200),
(1900,150),
(2000,290),
(2000,310),
(5000,800),
(5000,820);
--select * from #rev;
--select * from #location;
;with
found_cte(RevisionId, [Status]) as (
select r.RevisionId, 'Found'
from #rev r join #location l on r.RevisionId=l.locRevisionId
and r.AssemblySiteId=l.locAssemblySiteId),
not_found_cte(RevisionId, [Status]) as (
select r.RevisionId, 'Not Found'
from #rev r
where not exists(
select 1 from #location l where r.RevisionId=l.locRevisionId
and r.AssemblySiteId=l.locAssemblySiteId)),
union_cte(RevisionId, [Status]) as (select * from found_cte union all select * from not_found_cte)
select RevisionId [Revision Id], [Status]
from union_cte
order by 1;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply