Home: Perl Programming Help: DBI:
SQL if then else situation


Mar 30, 2014, 5:30 AM

Views: 39748
SQL if then else situation

I have a simple table with 3 columns: Account, Ticker, and Amount. Account and Ticker are the keys.

There may be a situation when I have to enter an Amount where I’m not certain if the Account and Ticker already exist.

Adding the row is a simple:
INSERT INTO Investments (Account, Ticker, Amount) VALUES (3,’ABC’,1324.55);

Updating a previous Amount is also simple:
UPDATE Investments SET Amount = 1324.55 WHERE Account = 3 AND Ticker = ‘ABC’;

What is the best way to ask:

IF Account and Ticker record already exists, set amount = 1324.55 ELSE insert the record

Veteran / Moderator

Mar 30, 2014, 7:13 AM

Views: 39747
Re: [PapaGeek] SQL if then else situation

Use a replace statement.

Or, use an insert statement with an "ON DUPLICATE KEY UPDATE" clause.

(This post was edited by FishMonger on Mar 30, 2014, 7:16 AM)


Mar 30, 2014, 8:43 AM

Views: 39743
Re: [FishMonger] SQL if then else situation

Thanks for the reply, but I did leave one thing out of my original post. Maybe I was embarrassed to say it, but I’m using Access from that group that goes in its own direction. If you don’t mind dirty words in the forum, it’s “Microsoft”.

I can find no reference to either REPLACE ot ON DUPLICATE KEY UPDATE in the documentation from Bill Gates.

I’ll give both of your suggestions a try just in case ODBC expands his concept of how to do things.

I love standards, there are so many to choose from!

If all else fails I can just query the table first to see if the record already exists. Not the best way, but it is the Microsoft way!

Veteran / Moderator

Mar 31, 2014, 3:28 PM

Views: 39647
Re: [PapaGeek] SQL if then else situation

Hmm, is this really a Perl question? I doubt...

But anyway...

In Reply To
If all else fails I can just query the table first to see if the record already exists. Not the best way, but it is the Microsoft way!

Nothing wrong with this approach. As far as I can say, the replace query is not a standard SQL command, but probably an extension available on only some relational database engines. If you don't have it, just do it in a two-step (or perhaps even three-step) procedure along the following lines:
- update it it exists, else create, or
- delete if it exists, then create.
After all, Perl is a procedural language that enables you to do thiunks that can't be done in pure SQL. In fact, I did something similar that recently in PL/SQL (so nothing to do with Microsoft, and, although I might be wrong, I do not think that a replace query exists in Oracle SQL). (I wish I could have done it in Perl with DBI, I would have saved quite a bit of time, but the client unfortunately demanded explicitely programs to be written in PL/SQL.)

(This post was edited by Laurent_R on Mar 31, 2014, 3:28 PM)