Board index » delphi » Local SQL Updating/Inserting using IF statement

Local SQL Updating/Inserting using IF statement

I want to Insert a record in a table if the record doesn't exists
before. If it already exists I want to update it instead.
I have a Query that looks like this:

IF EXISTS (SELECT field1, field2, field3 FROM table1
WHERE field1=123 and field2 = 'ABC')
  UPDATE table1 SET field3='XYZ'
ELSE
  INSERT INTO table1 (field1,field2,field3)  
  VALUES (123, 'ABC', ',XYZ')

I have used the Query with MS SQL 7 without any problems,  but when I
try to execute it on a Paradox table using Local SQL I get this error
msg:

Invalid use of keyword
Token: if
Line number: 1

Anyone who knows what the problem is?

Regards Jonas Karlsson

 

Re:Local SQL Updating/Inserting using IF statement


Yes... the Paradox support for SQL is not nearly so sophisticated as
this.

Paradox does not have true SQL support.  And unfortunately, the other
strong contender in the SQL arena on the desktop, MS-Access/JET, does
not have _this sort of thing either.

You're going to have to do an equivalent thing in application code ...
or else re-evaluate the whole decision to try to use Paradox tables here
if you've already got the thing working with MS-SQL.  "How many other
situations like this are out there?"  Ask yourself this question, and
find out the answer, before you proceed much farther.  

Calculate the number of hours it will take to deal with all the cases
you've found, add 25% contingency for the ones you didn't find,
calculate the cost (conservatively) at $75.00 per hour, and then decide
what to do next.  

I am perfectly serious.  Whether you own the business or are simply
employed by it, _someone is going to need to make that decision and do
it in exactly those terms.  May as well be you.  :-}

Quote
>Jonas Karlsson wrote:

> I want to Insert a record in a table if the record doesn't exists
> before. If it already exists I want to update it instead.
> I have a Query that looks like this:

> IF EXISTS (SELECT field1, field2, field3 FROM table1
> WHERE field1=123 and field2 = 'ABC')
>   UPDATE table1 SET field3='XYZ'
> ELSE
>   INSERT INTO table1 (field1,field2,field3)
>   VALUES (123, 'ABC', ',XYZ')

> I have used the Query with MS SQL 7 without any problems,  but when I
> try to execute it on a Paradox table using Local SQL I get this error
> msg:

> Invalid use of keyword
> Token: if
> Line number: 1

> Anyone who knows what the problem is?

> Regards Jonas Karlsson

--
----------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
mailto:i...@sundialservices.com  (PGP public key available.)

- Show quoted text -

Quote
> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  Release 4.0 is here!!
> http://www.sundialservices.com/products/chimneysweep

Other Threads