SYBASE: Syntax to add a column for a table

To check Results through log file for adding a column in a table below syntax can be used.

use your_database

go

SELECT ' *** ADD New column new_column in your_database..table_name table *** '

go

IF NOT EXISTS (SELECT 1 FROM syscolumns sc, sysobjects so

                WHERE so.id = sc.id

                AND sc.name = 'new_column'

                AND so.name = 'table_name')

    BEGIN

        SELECT ' *** Start Adding New column *** '

       

        ALTER TABLE table_name

            ADD new_column VARCHAR(255) NULL

    END

ELSE

    BEGIN

        SELECT ' *** new_column column already exists in your_database..table_name *** '

    END               

go   

SELECT ' *** Check New Column Existense *** '

go

SELECT so.name, sc.name from sysobjects so, syscolumns sc

where so.id = sc.id

and so.name = 'table_name'

and sc.name = 'new_column'

go

NOTE: By changing IF NOT EXISTS to IF EXISTS we can use this to modify existing columns.

SYBASE:How To use Case statement in UPDATE Clause

Below is the syntax to use case statement in UPDATE Clause:

create table #tmp1 (col1 INT NULL, col2 INT NULL)

INSERT INTO #tmp1 values (1,1)

INSERT INTO #tmp1 values (2,1)

INSERT INTO #tmp1 values (3,2)

INSERT INTO #tmp1 values (4,1)

UPDATE #tmp1

SET col1 = CASE WHEN t.col1 NOT IN (SELECT t2.col FROM #tmp2 t2)

                    THEN 9

                ELSE 5

           END

FROM  #tmp1 t

WHERE t.col2 = 1     

NOTE: Make sure to use END as the closing command for the CASE statement.