mhlover mhlover - 7 months ago 14
SQL Question

Setting a column based on conditions when INSERTING data in ORACLE

Currently working on a basketball performance database. The issue I'm having is storing the winner of a match.
Match table is currently like so:

CREATE TABLE Matches(
M_ID int CONSTRAINT pk_Match PRIMARY KEY,
M_Date Date NOT NULL,
M_Location varchar(20),
M_HomeTeam int NOT NULL,
M_AwayTeam int NOT NULL,
M_HomeScore int NOT NULL,
M_AwayScore int NOT NULL,
M_Winner int,
CONSTRAINT fk_TeamHome foreign key (M_HomeTeam) REFERENCES Team(T_ID),
CONSTRAINT fk_TeamAway foreign key (M_AwayTeam) REFERENCES Team(T_ID)
)


What I want is the value of M_Winner to be set to M_HomeTeam & M_AwayTeam foreign keys based on their scores.
I've been able to do this with this update statement

UPDATE Matches
SET M_Winner = CASE
WHEN M_HomeScore > M_AwayScore
THEN M_HomeTeam
WHEN M_AwayScore > M_HomeScore
THEN M_AwayTeam
END;


However I need it to work when the data is inserted.
Any ideas?

Answer

In Oracle 11g+, you can use a virtual computed column:

ALTER TABLE matches
    ADD m_winner as (CASE WHEN M_HomeScore > M_AwayScore
                          THEN M_HomeTeam
                          WHEN M_AwayScore > M_HomeScore
                          THEN M_AwayTeam
                     END)