CODEWITHSUNDEEP
sqlsql-serverdatabaseauto-populate
gogo_rulez
gogo_rulez

Reputation: 389

Populate extra database column depending on other column values

I have a database which collects data from an application. And now, I have to create another column that will be populated with predefined data depending on the values in other columns. So, no math, just to look up the values in two other columns and insert the data into the newly added column.

Example

id     column1     column2    newColumn
1      15          3          100

So when column1 has 15, and column2 has 3, the newColumn should be auto-populated with 100. Again, the number 100 is predifned, not calcualted.

I know I can use triggers for new entries, but the database already has a large amount of data entered, so is there a way to auto populate the newColumn for data that is already tere?

EDIT --------------------------------

So I can use update to populate the column for the records that are already entered ?!

Can i make a trigger which will wait for both values and until both are entered it will return NULL?

Upvotes: 0

Views: 2809

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can create scalar function:

ALTER FUNCTION [dbo].[Test] ( @column1 INT, @column2 INT)
RETURNS INT
    WITH SCHEMABINDING
AS
    BEGIN

        DECLARE @r INT

        IF @column1 = 15 AND @column2 = 3
            SET @r = 100
        ELSE
            SET @r = NULL

        RETURN @r
    END

And then add new computed column:

ALTER TABLE TableName ADD ColumnName AS dbo.Test(column1, column2) PERSISTED

Persisted means, that column is not calculated on the fly, but data is saved. That's why you used WITH SCHEMABINDING. Without binding you can not make the column persisted.

You can also update your current data with simple update statement like in @Rhys Jones answer and add trigger on table like:

ALTER TRIGGER trTest ON TableName
AFTER INSERT, UPDATE
AS
BEGIN
    IF UPDATE(column1) AND UPDATE(column2)
        BEGIN
            UPDATE  TableName
            SET     NewColumn = CASE
                                  WHEN column1 = 15 and column2 = 3 then 100
                                  ELSE NULL
                                END
            FROM    Inserted i
                    JOIN TableName t ON t.id = i.id
        END
END

Upvotes: 3

Rhys Jones
Rhys Jones

Reputation: 5508

You could just use a single UPDATE to update the missing values, then use the TRIGGER for new rows.

    update MyTable set
        newColumn = case 
                        when column1 = 15 and column2 = 3 then 100
                        when ...
                    end

    where
        newColumn is null

However, note what @jarlh says above, there are usually better ways of doing this such as views or computed columns.

Upvotes: 0

Related Questions