CODEWITHSUNDEEP
sqlsql-server
sauabhi
sauabhi

Reputation: 11

How to update values in one column based on the values in another column

I am new to SQL and still learning the ins and out of the DBMS.

I have a table with a column called ApprovalID that is of type int and has values 1-5. (These values are updated by the code running on the website. I have another column called Approval that I replicated from the ApprovalID column values but it is of varchar type and displays strings (Hatchery for 1, Purchase for 2, Rearing for 3, Source Unknown for 4 and Not Approved for 5).

Right now the Approval column doesn't update on its own, I have to run queries manually to change the values in it if the corresponding values in ApprovalID column have changed.

I need a solution to either automatically update the values in Approval if values in ApprovalID have changed, but instead of 1,2,3,4,5 I want to display the strings mentioned above.

Please help.

Upvotes: 1

Views: 143

Answers (2)

fastobject
fastobject

Reputation: 1430

You can use several approuches to do this:

Computed Columns

CREATE TABLE t3
(
    ID INT IDENTITY(1,1),
    ApprovalID INT,
    Approval AS ( CASE 
                    WHEN ApprovalID = 1 THEN 'Hatchery'
                    WHEN ApprovalID = 2 THEN 'Purchase' 
                    WHEN ApprovalID = 3 THEN 'Rearing' 
                    WHEN ApprovalID = 4 THEN 'Source Unknown' 
                    WHEN ApprovalID = 5 THEN 'Not Approved'
                    ELSE '<Unknown approval state>' 
                END
        )  PERSISTED
)

INSERT INTO t3(ApprovalID) VALUES(1)
INSERT INTO t3(ApprovalID) VALUES(2)
INSERT INTO t3(ApprovalID) VALUES(3)
INSERT INTO t3(ApprovalID) VALUES(4)
INSERT INTO t3(ApprovalID) VALUES(5)
UPDATE t3 
    set ApprovalID = 2
    where ApprovalID = 1
SELECT * FROM t3

Triggers

DROP TABLE t3
CREATE TABLE t3
(
    ID INT IDENTITY(1,1),
    ApprovalID INT,
    Name NVARCHAR(256),
)
GO
CREATE TRIGGER t3_trigger
    ON t3
    AFTER INSERT, UPDATE
    AS
    UPDATE t3 
        SET Name = CASE 
                WHEN i.ApprovalID = 1 THEN 'Hatchery'
                WHEN i.ApprovalID = 2 THEN 'Purchase' 
                WHEN i.ApprovalID = 3 THEN 'Rearing' 
                WHEN i.ApprovalID = 4 THEN 'Source Unknown' 
                WHEN i.ApprovalID = 5 THEN 'Not Approved'
                ELSE '<Unknown approval state>' 
            END
    FROM t3 INNER JOIN inserted i ON t3.ID = i.ID
GO
INSERT INTO t3(ApprovalID) VALUES(1)
INSERT INTO t3(ApprovalID) VALUES(2)
INSERT INTO t3(ApprovalID) VALUES(3)
INSERT INTO t3(ApprovalID) VALUES(4)
INSERT INTO t3(ApprovalID) VALUES(5)

UPDATE t3 set ApprovalID = 2 WHERE ApprovalID = 1

SELECT t3.*, Approval.Name FROM t3 LEFT JOIN Approval ON t3.ApprovalID = Approval.ApprovalID

But best way is to split data on two table

CREATE TABLE t3
(
    ID INT IDENTITY(1,1),
    ApprovalID INT,
)

CREATE TABLE Approval
(
   ApprovalID INT,
   Name NVARCHAR(256)
)

INSERT INTO Approval(ApprovalID, Name) VALUES( 1, 'Hatchery' )
INSERT INTO Approval(ApprovalID, Name) VALUES( 2 , 'Purchase')
INSERT INTO Approval(ApprovalID, Name) VALUES( 3, 'Rearing' ) 
INSERT INTO Approval(ApprovalID, Name) VALUES( 4, 'Source Unknown' ) 
INSERT INTO Approval(ApprovalID, Name) VALUES( 5, 'Not Approved' )

INSERT INTO t3(ApprovalID) VALUES(1)
INSERT INTO t3(ApprovalID) VALUES(2)
INSERT INTO t3(ApprovalID) VALUES(3)
INSERT INTO t3(ApprovalID) VALUES(4)
INSERT INTO t3(ApprovalID) VALUES(5)

and use for select data query like below:

SELECT t3.*, Approval.Name FROM t3 LEFT JOIN Approval ON t3.ApprovalID = Approval.ApprovalID

Upvotes: 0

db9dreamer
db9dreamer

Reputation: 1715

instead of:-

select approvalid, approval, othercolumn, anothercolumn
from tablea

do this once:-

create tableb (
    approvalid int,
    approval varchar(50)
)

insert into tableb values (1, 'Hatchery')
insert into tableb values (2, 'Purchase')
insert into tableb values (3, 'Rearing')
insert into tableb values (4, 'Source Unknown')
insert into tableb values (5, 'Not Approved')

drop the approval column from tablea and then do this, when you need the descriptions:-

select a.approvalid, b.approval, a.othercolumn, a.anothercolumn
from tablea a
join tableb b on b.approvalid=a.approvalid

this is a beginning. there are many things you also need to learn about like primary keys and indexes...

Upvotes: 1

Related Questions