how do you do "if record doesn't exist create otherwise update" in one SQL statement?

[Mike]

how do you do "if record doesn't exist create otherwise update" in one
SQL statement?

-Mike

[Ken]

I don't think you can do this in straight ANSI SQL. If you are using
something like T-SQL (Transact SQL) or PLSQL then it's possible. What DBMS
are you using?

Cheers
Ken

"Mike" <com> wrote in message
news:google.com... 

[Aaron]

Assuming SQL Server (never a bad idea to tell us which database you're
using; "SQL" is pretty generic and ambiguous).

UPDATE table SET ... WHERE ...
IF @@ROWCOUNT = 0
INSERT table(col_list) SELECT val_list

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Mike" <com> wrote in message
news:google.com... 

[Mike]

"Aaron [SQL Server MVP]" <noraa> wrote in message news:<#t#phx.gbl>... 

I'm using Access 2000

Here's my statement:

UPDATE MiniStatData SET statValue = NULL WHERE statId = 1 IF
@@ROWCOUNT = 0 INSERT MiniStatData(miniId, statId, statValue) SELECT
309, 1, NULL

I get a "Syntax error (missing operator) in query expression ..."
error when I try running it. Is it because I am using Access or did I
write something wrong?

Thanks,
Mike

[Aaron]

> UPDATE MiniStatData SET statValue = NULL WHERE statId = 1 IF 

Yes, it's because you are using Access. My post said "Assuming SQL
Server"...

With Access, you might have to do it this way:

set rs = conn.execute("SELECT COUNT(*) FROM table WHERE ...")
if rs(0) > 0 then
' run update
else
' run insert
end if

Maybe Bob could show us a stored query that does the same thing. I'm not
too big on VBA or how multi-statement stored queries work, because Access is
far from my cup 'o' tea.

--
http://www.aspfaq.com/
(Reverse address to reply.)

[Bob]

Aaron [SQL Server MVP] wrote: 
>
> Yes, it's because you are using Access. My post said "Assuming SQL
> Server"...
>
> With Access, you might have to do it this way:
>
> set rs = conn.execute("SELECT COUNT(*) FROM table WHERE ...")
> if rs(0) > 0 then
> ' run update
> else
> ' run insert
> end if
>
> Maybe Bob could show us a stored query that does the same thing. I'm
> not too big on VBA or how multi-statement stored queries work,
> because Access is far from my cup 'o' tea.[/ref]

Nope. No such thing as UPSERT.

In addition, Jet does not support batched queries. You have to send one
statement at a time.

Yet another reason to switch to SQL Server if high throughput is needed.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron]

> Nope. No such thing as UPSERT.

<whine>
Aw, but Oracle has it (MERGE)!
</whine>

;-) for the humor impaired.

--
http://www.aspfaq.com/
(Reverse address to reply.)