A sort of IF THEN ELSE in SQL

[Laphan]

Hi All

I know this isn't an SQL syntax NG per say (esp MySQL), but there are far
more legible gurus in here than the MySQL groups so I hope you will bear
with me.

All I want to know is if there is a sort of IF ... THEN ... ELSE command in
MySQL? The reason being that if a product code exists then I want to do an
update, but if it doesn't then I want to do an insert.

At the moment, I'm having to do a Select query first to see if it does exist
and then do a 2nd update/insert query depending on the result I get from the
1st.

I'd like to sort this into one query if poss so your feedback would be
appreciated.

Rgds

Laphan

[Aaron [SQL Server MVP]]

> At the moment, I'm having to do a Select query first to see if it does
exist

> and then do a 2nd update/insert query depending on the result I get from

the

> 1st.

You're going to have to do that anyway, I think. In SQL Server, I do this:

IF EXISTS (SELECT 1 FROM tbl WHERE <logic to determine uniqueness>)
UPDATE tbl ... WHERE <logic to determine uniqueness>
ELSE
INSERT ...

A roughly performance-equivalent alternative is:

UPDATE tbl ... WHERE <logic to determine uniqueness>
IF @@ROWCOUNT = 0
INSERT ...

For MySQL specifics (e.g. whether the EXISTS clause exists, or something
similar), you'll probably have better luck in a MySQL newsgroup or from the
MySQL docs.

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

[Laphan]

Thanks for the feedback Aaron

Much appreciated.

Rgds

Laphan


Aaron [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote in message
news:e0ECx$IWEHA.2696@TK2MSFTNGP09.phx.gbl...

> At the moment, I'm having to do a Select query first to see if it does

exist

> and then do a 2nd update/insert query depending on the result I get from

the

> 1st.

You're going to have to do that anyway, I think. In SQL Server, I do this:

IF EXISTS (SELECT 1 FROM tbl WHERE <logic to determine uniqueness>)
UPDATE tbl ... WHERE <logic to determine uniqueness>
ELSE
INSERT ...

A roughly performance-equivalent alternative is:

UPDATE tbl ... WHERE <logic to determine uniqueness>
IF @@ROWCOUNT = 0
INSERT ...

For MySQL specifics (e.g. whether the EXISTS clause exists, or something
similar), you'll probably have better luck in a MySQL newsgroup or from the
MySQL docs.

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