469,357 Members | 1,870 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,357 developers. It's quick & easy.

SQL: IF EXISTS... using TEXT field

I'm trying to use a IF EXISTS (SELECT...) UPDATE... ELSE INSERT... sql
query.

This works until I try to use it with a text field type. When I do that, I
get an error saying ntext can't be used in this type of aggregate query.

Ugh.

So, is there solution to this via SQL? Or do I manually need to write three
separate queries and connections: the first to see if it exists, thena
separate UPDATE or INSERT function?

-Darrel
May 27 '06 #1
4 2179
dmm
not sure this is the correct group, but you should use 'if select 1 from xxx
where xxxx' as in the code below:
--won't work
if exists
(
select
cast ('fred' as ntext)
where 1 = 1
)
begin
print 'exists'
end
else
print 'doesnt exist'

--so use select 1 with the same where conditions
if exists
(
select
1
where 1 = 1

)
begin
print 'exists'
end
else
print 'doesnt exist'
HTHs

"Darrel" <no*****@nospam.com> wrote in message
news:%2***************@TK2MSFTNGP03.phx.gbl...
I'm trying to use a IF EXISTS (SELECT...) UPDATE... ELSE INSERT... sql
query.

This works until I try to use it with a text field type. When I do that, I
get an error saying ntext can't be used in this type of aggregate query.

Ugh.

So, is there solution to this via SQL? Or do I manually need to write
three separate queries and connections: the first to see if it exists,
thena separate UPDATE or INSERT function?

-Darrel

May 27 '06 #2
> not sure this is the correct group, but you should use 'if select 1 from
xxx where xxxx' as in the code below:


I'm not quite following that. I'm not using a SP, btw. Here's my actual
query I'd like to use:

IF EXISTS (SELECT varType, longVarValue FROM table WHERE varType =
'siteMenu') UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)

With longVarValue being of type 'text' in the DB. Do I need to case that as
'ntext'?

-Darrel
May 27 '06 #3
dmm
Sorry, poor communication skillz ;)

I was trying to point out that actually it is the EXISTS keyword that
doesn't like being given NText. Rather than selecting out the actual value,
if you keep your origonal WHERE predicates you may simply select 1 (logical
true). The rest of your query may remain the same.

try the following:

IF EXISTS
(
SELECT
1
FROM
table
WHERE
varType = 'siteMenu'
)
UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE
INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)

"Darrel" <no*****@nospam.com> wrote in message
news:u8**************@TK2MSFTNGP02.phx.gbl...
not sure this is the correct group, but you should use 'if select 1 from
xxx where xxxx' as in the code below:


I'm not quite following that. I'm not using a SP, btw. Here's my actual
query I'd like to use:

IF EXISTS (SELECT varType, longVarValue FROM table WHERE varType =
'siteMenu') UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)

With longVarValue being of type 'text' in the DB. Do I need to case that
as 'ntext'?

-Darrel

May 27 '06 #4
> I was trying to point out that actually it is the EXISTS keyword that
doesn't like being given NText.


Argh! USER ERROR! ;o)

I completely missed that. Thanks for pointing it out!

-Darrel
May 27 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Peter Bailey | last post: by
20 posts views Thread by Bryan | last post: by
8 posts views Thread by Yitzak | last post: by
4 posts views Thread by Neil | last post: by
reply views Thread by Medhatithi | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.