SQL queries regarding following conditions-CodePudding

Suppose I have these two tables: StudentTable

CREATE TABLE [dbo].[StudentTable] 
(
        [StId]           INT          IDENTITY (1, 1) NOT NULL,
        [StName]         VARCHAR (50) NOT NULL,
        [StSemester]     INT          NOT NULL,
        [StDep]          VARCHAR (50) NOT NULL,
        [StPhone]        INT          NOT NULL,
        [StDOB]          DATE         NOT NULL,
        [StGender]       VARCHAR (50) NOT NULL,
        [StCurrentFees]  INT          NULL,
        [StOldFees]      INT          NULL,
        [StTotalPayment] INT          NULL,
        [FId]            INT          NULL,

        PRIMARY KEY CLUSTERED ([StId] ASC),

        CONSTRAINT [FK1] 
            FOREIGN KEY ([FId]) REFERENCES [dbo].[FeesUpdateTable] ([FId])
);

CREATE TABLE [dbo].[FeesUpdateTable] 
(
    [FId]        INT          IDENTITY (1, 1) NOT NULL,
    [Department] VARCHAR (50) NOT NULL,
    [Semester]   INT          NOT NULL,
    [Fees]       INT          NOT NULL,

    PRIMARY KEY CLUSTERED ([FId] ASC)
);

I want to update StCurrentFees = Fees where StSemester = Semester and StDep = Department.

What would be the query for it? Any answer would be appreciated

CodePudding user response：

You can use the below query.

UPDATE Student
INNER JOIN Fees ON Fees.FId= Student.FId
SET Student.SFees= Fees.FFees 
WHERE Student.SSem = Fees.FSem and Student.SDep = Fees.FDep;

CodePudding user response：

Try the following:

UPDATE S
SET S.StCurrentFees = F.Fees 
From StudentTable S 
JOIN FeesUpdateTable F 
ON S.StSemester = F.Semester
And S.StDep = F.Department

See a demo using SQL Server.