Solved

IF not exists query

Posted on 2014-12-15
2
74 Views
Last Modified: 2014-12-15
Can someone take a look at my sql below. It is probably more like Pseudo code right now, because
I did not include all of the script. Take a look at lines 14 and lines 17. I do an if exists and a select into PersonId
Can I take the value in PersonId at line 14 and put it into the value TargetTable.PersonId at line 17 ?


1  OPEN CURS_EMP
2  @BreakOutOfLoop INT

3  SELECT @lchFITS_SUFFIX = CONVERT(INT, @lchSuffix)
4  FETCH NEXT FROM CURS_EMP
5  INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX
6  SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX+1
7

8 WHILE @@FETCH_STATUS = 0
9 BEGIN
10      SET @SnafterIdIsUnique = 0
11
12      WHILE(@SnafterIdIsUnique = 0)
13       BEGIN
14          IF NOT EXISTS(SELECT PersonsId =  GetRandomId())
15          BEGIN
16                UPDATE TargetTable
17                SET TargetTable.PersonId = PersonId
18                SET @SnafterIdIsUnique = 1

19          END
20       END
0
Comment
Question by:brgdotnet
2 Comments
 
LVL 7

Accepted Solution

by:
Phil Davidson earned 500 total points
ID: 40501852
What version of SQL Server is this?  There is a phrase "my sql" in the comment.

I would rewrite lines 14 through 18 to this:

SET @specialVar = ISNULL(PersonID, GetRandomID())
UPDATE TargetTable
                SET TargetTable.PersonId = @specialVar
                SET @SnafterIdIsUnique = 1

Open in new window


If it is MySQL, the ISNULL should be IFNULL.

Lines 8 through 18 as they were written seem verbose.  Maybe there is a reason to do it like that.
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40501885
No not MySQL. I was taking sql in the possessive sense, like "My sql script"
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now