0

I work on SQL server 2017 I have table #Pressfeature as below

create table #Pressfeature
  (
  PartId int,
  PressName varchar(300),
  TechId int
  )
   insert into #Pressfeature(PartId,PressName,TechId)
   values
   (1211,'AC',1),
   (2421,'grail',2),
   (6211,'compress',3)

my issue Done For Part id 3900 it take wrong

TechId 5 and Correct Must be 2 Because Press Name Exist as Press Name grail.

If Press Name Not Exist it will Take Maximum Number + 1 meaning 4 as Press Name Angit

   +--------+--------------+---------------+-------------
    | PartID |  PressName   |   TechId  
    +--------+--------------+---------------+-------------
    |   3900 | grail        |   2 (Exist Before )
    +--------+--------------+---------------+-------

   +--------+--------------+---------------+-------------
    | PartID |  PressName   |   TechId  
    +--------+--------------+---------------+-------------
    |   8321 | Angit        |   4 (New and Not Exist)
    +--------+--------------+---------------+-------

what I try is

 insert into #Pressfeature(PartId,PressName,TechId)
select  PartId,PressName,
        TechId  =  dense_rank() over (order by PressName)
                      + (select max(TechId) from #Pressfeature)
from    
(
        values
        (3900,'grail',NULL),
        (8321,'Angit',NULL)
) s (PartId,PressName,TechId)

Expected Result After Insert Data

PartId FeatureName TechnologyId
3900 grail         2
8321 Angit         4
1

1 Answer 1

0

If I understood you correctly, you want to keep the TechId for different PartIds with an existing PressName. If that is what you want, I think you're missing a reference to the original table - the way the query is written, it will only calculate the new TechId rather than reuse the existing one. To do that, you can LEFT JOIN to the original table on the PressName and then COALESCE the inserted TechId.

Like this:

select  s.PartId,s.PressName,
        TechId = COALESCE
        (
           pf.TechId,
           dense_rank() over (order by s.PressName) + (select max(TechId) from #Pressfeature)
        )
from    
(
        values
        (3900,'grail',NULL),
        (8321,'Angit',NULL)
) s (PartId,PressName,TechId)
LEFT JOIN #Pressfeature pf ON pf.PressName = s.PressName;

db<>fiddle here

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.