Post reply

Check if table exists

  • February 16, 2008 at 1:36 pm

    #378655

    Hi,

    I'm trying to update a single row table (Transaction_Status) with a new value.

    The field to update is Current_Status.

    The update is made when the table ADDANNEX exists. The problem is that I have 2 occurences of ADDANNEX

    NEW.ADDANNEX

    and

    Control.ADDANNEX

    in the same database.

    How do check that NEW.ADDANNEX exists?

    The base table code below works but doesn't discriminate.

    UPDATE Control.Transaction_Status

    SET Control.Transaction_Status.Current_Status = '1'

    WHERE EXISTS (SELECT 1

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE='BASE TABLE'

    AND TABLE_NAME='ADDANNEX') AND

    Control.Transaction_Status.Current_Status = '0'

    Any ideas?

    Thanks in advance,

    N

  • February 16, 2008 at 2:38 pm

    #779124

    Checking if the table exists prior to the update or modifying the subquery are two options.

    -- Option 1: Check for table before updating.

    IF OBJECT_ID('mySchema.myTable','U') IS NOT NULL

    BEGIN

    -- UPDATE operation

    END

    -- Option 2: Modify the subquery

    SELECT t1.name

    FROM sys.tables t1

    JOIN sys.schemas t2 ON t2.schema_id = t1.schema_id

    WHERE t2.name = 'mySchema'

    AND t1.name = 'myTable'

  • February 16, 2008 at 2:45 pm

    #779125

    UPDATEts

    SETts.Current_Status = '1'

    FROMTransaction_Status AS ts

    WHEREts.Current_Status = '0'

    AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'ADDANNEX' AND TABLE_SCHEMA = 'NEW')

    N 56°04'39.16"
    E 12°55'05.25"

  • February 16, 2008 at 10:02 pm

    #779147

    EDIT:

    I originally (incorrectly :blush: ) suggested do something like this... but, as Peso pointed out below, it's going to throw an error if table doesn't actually exist. My appologies for the error... I shouldn't post late at night...

    UPDATEts

    SETts.Current_Status = '1'

    FROMTransaction_Status AS ts

    WHEREts.Current_Status = '0'

    AND EXISTS (SELECT 1 FROM New.AddAnnex)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 17, 2008 at 3:06 am

    #779163

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'New.AddAnnex'.

    N 56°04'39.16"
    E 12°55'05.25"

  • February 17, 2008 at 6:20 am

    #779170

    Thanks guys,

    The day was saved!!

    Much appreciated.

  • February 17, 2008 at 8:24 am

    #779180

    Peso (2/17/2008)

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'New.AddAnnex'.

    Doh!... Thanks for the correction, Peter... that's what I get for late night posting. I'll correct that post...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply