IF EXISTS UPDATE ELSE UPDATE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF EXISTS UPDATE ELSE UPDATE

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-18 : 13:54:53

Hi Experts,
I want to update tableA if certain condition is met. this condition has joins for three tables. and the mattching values should update the tableA is certain way whereas if that condition is not met, it should update the TableA in different way.

I was wondering if I can use
IF EXISTS ( select ......)
UPDATE TableA

ELSE
UPDATE TableA
?

Here is the query:
------------------------------------------------------------
if exists (select qtdrv.sysid from TestDriver as QTDRV
inner join dbo.Testprov as QTPSP
on QTPSP.sysID=QTDRV.sysID
inner join Testprovider as QTPRO
on QTpro.sysID = QTPSP.sysID
and QTPSP.MatchID = QTPRO.MatchID

)
update dbo.TestDriver
set Refid = QTPRO.Refid
from dbo.Testprovider as QTPRO
join dbo.Testprov as QTPSP
on QTPRO.MatchID = QTPSP.MatchID
and QTPSP.sysID=QTPRO.sysID
join dbo.TestDriver as QTDRV
on QTDRV.sysID = QTPSP.sysID

ELSE

update dbo.TestDriver
set refid = PPRSP.provid
from dbo.prov as PPRSP
join dbo.Testprovspecialty as QTPSP
on QTPSP.code = PPRSP.code
and QTPSP.type=PPRSP.type
join dbo.TestDriver as QTDRV
on QTDRV.sysID = QTPSP.sysID

Any suggestion will be appreciated !!
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-18 : 14:16:04

I suspect that this may not be what you need - but hard for me to say because I didn't quite understand the business logic that you want to implement. The "IF EXISTS" condition will return true if the query in the if exists clause returns at least one row.

Before you use the query to update, change the updates to select and run them to see if you are getting the rows and the data that you want to update.

IF EXISTS (
       SELECT
       	qtdrv.sysid
       FROM
       	TestDriver AS QTDRV
       	INNER JOIN dbo.Testprov AS QTPSP
       		ON  QTPSP.sysID = QTDRV.sysID
       	INNER JOIN Testprovider AS QTPRO
       		ON  QTpro.sysID = QTPSP.sysID AND QTPSP.MatchID = QTPRO.MatchID
   )
    --UPDATE dbo.TestDriver
    --SET    Refid = QTPRO.Refid
    SELECT *
    FROM   dbo.Testprovider AS QTPRO
           JOIN dbo.Testprov AS QTPSP
                ON  QTPRO.MatchID = QTPSP.MatchID
                AND QTPSP.sysID = QTPRO.sysID
           JOIN dbo.TestDriver AS QTDRV
                ON  QTDRV.sysID = QTPSP.sysID
ELSE
    --UPDATE dbo.TestDriver
    --SET    refid = PPRSP.provid
    SELECT *
    FROM   dbo.prov AS PPRSP
           JOIN dbo.Testprovspecialty AS QTPSP
                ON  QTPSP.code = PPRSP.code
                AND QTPSP.type = PPRSP.type
           JOIN dbo.TestDriver AS QTDRV
                ON  QTDRV.sysID = QTPSP.sysID

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-18 : 21:58:23

Please post DDL with specs and not narratives or an invented language. We need keys and constraints. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, industry standard codes and so forth. Let us know if you can changed the DDL.

Good SQL programer do not use the old UPDATE.. FROM.. syntax because of cardinality problems and lack of portability. My guess is that the ref_id should be done in a VIEW, so it is always current and not embedded as a physically redundant value. Data element names like “type” and “code” are so generic and vague, they are useless. things like "sys_id" are scary because they sound like an EAV design

Good SQL programmers seldom use IF-THEN control flow – that was BASIC, FORTRAN and COBOL. We have CASE expressions and have something like this skeleton:

UPDATE Test_Drivers
SET ref_id
= CASE WHEN ..
THEN QT_PRO.ref_id
WHEN ..
THEN PPRSP.prov_id
ELSE ref_id END
WHERE ..;

The single statement can be optimized and you do not have to waste resources on self-joins in teh FROM clause. Want to try again, but with basic Netiquette?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-18 : 23:30:12

quote:
Originally posted by jcelko

Good SQL programer do not use the old UPDATE.. FROM.. syntax because of cardinality problems and lack of portability.

Rubbish

quote:
My guess is that the ref_id should be done in a VIEW

Almost always a bad idea.

quote:
Data element names like “type” and “code” are so generic and vague, they are useless.

Bullshit.

quote:
things like "sys_id" are scary because they sound like an EAV design

yawwwwn.

quote:
Good SQL programmers seldom use IF-THEN control flow

Not true.

quote:
--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

SPAM

Have a nice evening Joe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources