INSERT or UPDATE

[Bingo]

Hi there.

I have this problem here :
I want to insert a record if it does not already exist, or to update it if
it does (based on index of course).
Can I do this with a simple SQL query?

Thanks!
Bingo

[Ray at]

How are you determining if the record exists? By a unique ID? Here's a
dirty method:

If objADO.Execute("select id from theTable where id=" & iLookForThis).EOF
Then
objADO.Execute "insert into theTable ([column1]) values ('" & sVal &
"')"
Else
objADO.Execute "update theTable set [column1]='" & sVal & "' where id="
& iLookForThis
End If


You may want something a little nicer though like:


iLookForThis = 34
sTheValue = "Bingo's value"
sTheValue = Replace(sTheValue, "'", "''")

Set rsDoesItExist = objADO.Execute("SELECT [Anything] FROM [YourTable] WHERE
[ID]=" & iLookForThis)
If rsDoesItExist.EOF Then '''record with this id does not exist
sSQL = "INSERT INTO [YourTable] ([SomeColumn]) VALUES ('" & sTheValue &
"')"
sResponse = "Your record was added."
Else '''record exists - udpate it instead
sSQL = "UPDATE [YourTable] SET [SomeColumn]='" & sTheValue & "' WHERE
[ID]=" & iLookFor
sResponse = "Your recoded was updated."
End If
rsDoesItExist.Close
Set rsDoesItExist = Nothing

objADO.Exeucte sSQL
objADO.CLose
Set objADO = Nothing

Response.Write sResponse


Ray at work
unproofread



"Bingo" <bingo@bingo.be> wrote in message
news:3f27d256$0$24565$626a54ce@news.free.fr...

> Hi there.
>
> I have this problem here :
> I want to insert a record if it does not already exist, or to update it if
> it does (based on index of course).
> Can I do this with a simple SQL query?
>
> Thanks!
> Bingo
>
>

[Aaron Bertrand - MVP]

Depends, what database???

In SQL Server, use a stored procedure that takes all the column values and
inputs, and uses the following code.


IF EXISTS (SELECT 1 FROM table WHERE <whatever makes this row "exist">)
UPDATE
ELSE
INSERT


You could also say:


UPDATE table ... WHERE <whatever makes this row "exist">
IF @@ROWCOUNT = 0
INSERT





"Bingo" <bingo@bingo.be> wrote in message
news:3f27d256$0$24565$626a54ce@news.free.fr...

> Hi there.
>
> I have this problem here :
> I want to insert a record if it does not already exist, or to update it if
> it does (based on index of course).
> Can I do this with a simple SQL query?
>
> Thanks!
> Bingo
>
>

[Bingo]

Hi Ray!

> How are you determining if the record exists? By a unique ID? Here's a

Not unique, but it doesn't change your code.

I'd like to do it without vb code though... But from your answer I
understand this is not possible... Right?

In fact, I'm pretty sure this is not possible, since the function (I just
found the answer in fact) was newly implemented in oracle9i as MERGE.
If access can do something to oracle9i couldn't do not so long ago, I would
be pretty much surprised...

Thanks for your help though!


Bingo.

[Ray at]

Well, you'll obviously need some vbS code (or js), but if you don't want to
do it all in vbs, you can use a stored procedure as Aaron suggested.

Ray at work

"Bingo" <bingo@bingo.be> wrote in message
news:3f27db43$0$24595$626a54ce@news.free.fr...

> Hi Ray!
>

> > How are you determining if the record exists? By a unique ID? Here's a

>
> Not unique, but it doesn't change your code.
>
> I'd like to do it without vb code though... But from your answer I
> understand this is not possible... Right?
>
> In fact, I'm pretty sure this is not possible, since the function (I just
> found the answer in fact) was newly implemented in oracle9i as MERGE.
> If access can do something to oracle9i couldn't do not so long ago, I

would

> be pretty much surprised...
>
> Thanks for your help though!
>
>
> Bingo.
>
>

[Bingo]

> IF EXISTS (SELECT 1 FROM table WHERE <whatever makes this row "exist">)

> UPDATE
> ELSE
> INSERT
>

Wow, I should switch to SQL-Server I guess! This looks like a powerfull
function!
I'm only using Access2000 though...
Can I do something that would look like your stored proc?

Thanks,
Bingo.

[Aaron Bertrand - MVP]

No, as Ray stated, you'll need to do this in VBS.

While Access supports stored queries, like SQL Server, I don't believe you
can have control logic like IF/END IF blocks.

Very curious that you would switch from Oracle to Access???





"Bingo" <bingo@bingo.be> wrote in message
news:3f27ddf2$0$24556$626a54ce@news.free.fr...

> > IF EXISTS (SELECT 1 FROM table WHERE <whatever makes this row "exist">)
> > UPDATE
> > ELSE
> > INSERT
> >

>
> Wow, I should switch to SQL-Server I guess! This looks like a powerfull
> function!
> I'm only using Access2000 though...
> Can I do something that would look like your stored proc?
>
> Thanks,
> Bingo.
>
>

[Bingo]

> Very curious that you would switch from Oracle to Access???

That would have been very curious indeed! :-)
No I never worked on Oracle in fact, I just found out that this SQL command
existed in Oracle9i, but I always worked with Access...

Thanks for your help both of you!

Bingo