CODEWITHSUNDEEP
sqlsql-serverwinformssql-server-2008stored-procedures
saurabh64
saurabh64

Reputation: 363

How to set only one value of column as true and rest false

I have a column in my database called PresentYear which is used to set a particular year as the academic year for the school for ex. 2014-15. I've added a default constraint which sets all values to 'N'. On assigncurrentyear button click I want to set it as 'Y'and when I set another year as PresentYear the previously set PresentYear should be reset to 'N' and only one row in the table should have 'Y'. I'll be passing academicID to set PresentYear and I am using a stored procedure to do so and I'm stuck here .

How should the stored procedure be?

create PROCEDURE [dbo].[USP_SetAcademicYear]
    @AcademicID INT
AS
BEGIN
    IF(@AcademicID != 0)
    BEGIN 
        UPDATE dbo.Academic_Master 
        SET presentYear = 'y'
        WHERE AcademicId = @AcademicID
    END
END

Upvotes: 0

Views: 1759

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

Just update both rows that need updating:

create PROCEDURE [dbo].[USP_SetAcademicYear]
    @AcademicID INT
AS
BEGIN
    IF(@AcademicID != 0)
    BEGIN 
        UPDATE dbo.Academic_Master 
        SET presentYear = CASE
             WHEN AcademicId = @AcademicID THEN 'y'
             ELSE 'N' END
        WHERE AcademicId = @AcademicID or presentYear = 'y'
    END
END

Upvotes: 2

hendryanw
hendryanw

Reputation: 1937

Use Not Equal To operator :

UPDATE Academic_Master
SET presentYear='N'
WHERE AcademicId <> @AcademicID

UPDATE : so your stored procedure will look like this

create PROCEDURE [dbo].[USP_SetAcademicYear]
@AcademicID INT
AS
BEGIN
    IF(@AcademicID!=0)
    BEGIN 
        update Academic_Master 
        set presentYear='y'
        WHERE AcademicId=@AcademicID

        UPDATE Academic_Master
        SET presentYear='N'
        WHERE AcademicId <> @AcademicID
    END
END

Upvotes: 2

Related Questions