Perl Guru Forums: Perl Programming Help: DBI: SQL if then else situation

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

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

PapaGeek
User

Mar 30, 2014, 5:30 AM

Post #1 of 4 (28981 views)

SQL if then else situation	Can't Post

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

FishMonger
Veteran / Moderator

Mar 30, 2014, 7:13 AM

Post #2 of 4 (28980 views)

Re: [PapaGeek] SQL if then else situation [In reply to]	Can't Post

Use a replace statement.
https://dev.mysql.com/doc/refman/5.0/en/replace.html

Or, use an insert statement with an "ON DUPLICATE KEY UPDATE" clause.
https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

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

PapaGeek
User

Mar 30, 2014, 8:43 AM

Post #3 of 4 (28976 views)

Re: [FishMonger] SQL if then else situation [In reply to]	Can't Post

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!

Laurent_R
Veteran / Moderator

Mar 31, 2014, 3:28 PM

Post #4 of 4 (28880 views)

Re: [PapaGeek] SQL if then else situation [In reply to]	Can't Post

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)

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives