SQL Server Query IF exist check issue

xdnvmnnf 于 2023-03-17 发布在其他

关注(0)|答案(1)|浏览(131)

I have three table FeesSetup, Fees, UserList.

FeesSetup Table has:

Tution Fee 50,
Exam Fee 30,
Other Fee 20

UserList Table:
All the userID stored in this table.

Fees Table:
Fees Generated here for all user

UserID FeesHead   Fee
1      Exam Fee   30
1      Tution Fee 50
1      Other Fee   20
2      Exam Fee   30
2      Tution Fee 50
2      Other Fee   20

I successfully generated all the fees for every user using this code

INSERT INTO Fees( FeesTypeID,FeesSetupID,FeesHeadID,Fee,MonthID,InstituteID,UserID)
SELECT  f.FeesTypeID,f.FeesSetupID,f.FeesHeadID,f.Fee,@MonthID,r.InstituteID,r.UserID
FROM    UserList r,FeesSetup f
WHERE   r.InstituteID = @InstituteID and f.InstituteID=@InstituteID and f.IsActive=1

But when new user added or fee change I try to regenerate fees, how can I regenerate fees?

I try this code to avoid duplicate issue but not working.

IF NOT EXISTS(SELECT * FROM Fees f, FeesSetup fs,Userlist u WHERE f.UserID= u.UserID and fs.FeesSetupID=f.FeesSetupID)
BEGIN
INSERT INTO Fees( FeesTypeID,FeesSetupID,FeesHeadID,Fee,MonthID,InstituteID,UserID)
SELECT  f.FeesTypeID,f.FeesSetupID,f.FeesHeadID,f.Fee,@MonthID,r.InstituteID,r.UserID
FROM    UserList r,FeesSetup f
WHERE   r.InstituteID = @InstituteID and f.InstituteID=@InstituteID and f.IsActive=1
END
ELSE
BEGIN
UPDATE Fees SET .....
END

Anyone please help.

sql-server

来源：https://stackoverflow.com/questions/75737646/query-if-exist-check-issue

1条答案

按热度按时间

qyzbxkaa1#

It looks like you could do this with MERGE .

WITH Source AS (
    SELECT
      f.FeesTypeID,
      f.FeesSetupID,
      f.FeesHeadID,
      f.Fee,
      @MonthID,
      r.InstituteID,
      r.UserID
    FROM UserList r
    JOIN FeesSetup f
      ON r.InstituteID = @InstituteID
     AND f.InstituteID = @InstituteID
     AND f.IsActive = 1
)
MERGE INTO Fees f WITH (HOLDLOCK)
USING Source s
  ON f.UserID   = s.UserID
 AND f.FeesHead = s.FeesHead
 AND f.MonthID  = s.MonthID
WHEN NOT MATCHED
  THEN INSERT
    (FeesTypeID, FeesSetupID, FeesHeadID, Fee, MonthID, InstituteID, UserID)
  VALUES
    (s.FeesTypeID, s.FeesSetupID, s.FeesHeadID, s.Fee, s.MonthID, s.InstituteID, s.UserID)
WHEN MATCHED THEN UPDATE
  SET Fee = s.Fee
;

Note that the ON clause must contain only the joining conditions, ie all columns which uniquely identify a row in the target table. Do not include any filtering conditions there, put those into a view or CTE instead.

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Query IF exist check issue

1条答案

相关问题

热门标签

最新问答