1

I am completely new to writing SQL scripts and could use a little help. I need to create a script that will run only once and add a column to an existing table. This column needs to have a value directly dependent to another column's value. The value is based on whether the other column's text value contains some text.

For instance, I need a way of saying for the following value "MNA-HLTH-CAR":

if (tableValue.contains("HLTH")) {
newColumn.insert("Health"); //Insert new column value Health
} else if (tableValue.contains......

At my attempt thus far I have:

ALTER TABLE [table_name]
ADD [new_element] varchar(5)
IF
    CONTAINS([column_name], "HLTH")

I probably am horrible off, and could use some help. Thanks!

Eddie D
  • 349
  • 1
  • 5
  • 16
  • What DB are you using ? MSSQL, MySQL, other ? And do you need one-time calculation - or one which would reflect later changes to dependent column ? – Agnius Vasiliauskas May 13 '13 at 13:45

1 Answers1

6

If the column is always dependant, and you're using a DBMS that supports it, you can use a computed column. For SQL Server, that looks like:

alter table YourTable
add YourColumn as
        (
        case
        when OtherColumn like '%Health%' then 'HLTH'
        else 'Default'
        end
        )

To add a real column with values, you could use a two-step approach. After adding the column:

alter table YourTable
add YourColumn varchar(5)

Run an update query to set its value:

update  YourTable
set     YourColumn =
        case
        when OtherColumn like '%Health%' then 'HLTH'
        else 'Default'
        end

That should initialize the new column. But unlike the computed column, it's now up to you to maintain it.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • If you are running the alter and the update in the same script, be sure to put a 'go' after the alter to commit the schema change, so the update can use the new column. – Scott Peterson Aug 04 '15 at 14:09