If Exist then Update

Neal · July 20th, 2007, 06:19 AM

Hi,

I want to Update a SQL Table value on condition that a 2nd Table exists.

ie.
IF Table2 exists

Code:

UPDATE dbo.Update_Status
    SET dbo.Update_Status.Status = '1'
WHERE EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME='Table2')

The above code produces (0) Rows Affected

How do I output the value of '1'?

Thanks in advance,

Neal

A Northern Soul

mmusleh · July 21st, 2007, 01:36 PM

I experienced unhealthy results when using EXISTS or NOT EXISTS so I switched to using COUNT(*); you can write your code a little bit different, use this syntax âI use it all the time and it works without issuesâ

If (SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE='BASE TABLE'
        AND TABLE_NAME='Table2') > 0
BEGIN
    UPDATE dbo.Update_Status
        SET dbo.Update_Status.Status = '1'
END

Database Analyst/Arch

Jeff Mason · July 21st, 2007, 03:41 PM

I'm curious what sort of "unhealthy results" you experienced.

The EXISTS predicate is much more efficient than computing a COUNT. It is simply a test for a non-empty set, whereas COUNT must actually go and, er, count things...

Jeff Mason
[email protected]

happygv · July 23rd, 2007, 12:41 AM

Neal,

Having ZERO as output for the Update Statement that you posted, I dont think TABLE2 exists. Did you try executing the subquery part separately to find if that works as expected?

Code:

SELECT 1 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME='Table2

Cheers

_________________________
- Vijay G
Strive for Perfection

Neal · July 25th, 2007, 07:49 AM

Thanks guys. Very much appreciated.

Neal

A Northern Soul