Can I use MySQL ifnull with a select statement?

Question

I'm trying the following and cannot find out what is wrong:

IF( IFNULL(
      SELECT * FROM vertreter AS ag 
      WHERE ag.iln = param_loginID 
      AND ag.verkaeufer = param_sellerILN
      ),
      UPDATE vertreter AS agUp
      SET agUp.vertreterkennzeichen
      WHERE agUp.iln = param_loginID AND agUp.verkaeufer = param_sellerILN
      ,
      INSERT INTO vertreter AS agIn
          ( agIn.iln, agIn.verkaeufer, agIn.vertreterkennzeichen, agIn.`status` )
      VALUES
          ( param_loginID, param_sellerILN, param_agentID, 'Angefragt' )
      );

Question:
Is this possible at all, to check if a SELECT returns NULL and then do A or B depending?

`IFNULL` can only be used with columns, not rows. What do you want to achieve? PS: you cannot mix updates with inserts and selects in one query — zerkms, Jun 21 '12 at 23:15
@zerkms: I need to check if an entry is in table "vertreter", if yes, I need to update the entry, if not, I need to make a new entry. The above is inside a stored proc already. Just can't get it to work. Thanks for help! — frequent, Jun 21 '12 at 23:19

zerkms · Accepted Answer · 2012-06-21T23:45:23.917

1

You need to create unique composite index (iln + verkaeufer).

CREATE UNIQUE INDEX vertreter_iln_verkaeufer ON vertreter (iln, verkaeufer)

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

And then you can do this in one query:

INSERT INTO vertreter
(agIn.iln, agIn.verkaeufer, agIn.vertreterkennzeichen, agIn.`status`)
VALUES (param_loginID, param_sellerILN, param_agentID, 'Angefragt')
ON DUPLICATE KEY UPDATE vertreterkennzeichen = param_agentID

Documentation: http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

edited Jun 21 '12 at 23:45

answered Jun 21 '12 at 23:25

zerkms

249,484
69
436
539

sounds good. Haven't head this before. So I'm inserting and if there is a matching record, I'm only updating? – frequent Jun 21 '12 at 23:29
1

@frequent: yes, but "matching record" is identified by unique index duplicate, so don't forget to create it (I mentioned that in my answer's first line) – zerkms Jun 21 '12 at 23:31
I'm lost. How do I declare the unique composite index? "iln" is the unique value, but where do I declare? – frequent Jun 21 '12 at 23:41
@zerksm: Thanks! I have to play around with this, because I don't know what I'm doing :-) – frequent Jun 21 '12 at 23:47
@zerksm: Seems to work. But I still don't understand why the composite index is needed. From your 2nd snippet, I'm inserting into "Vertreter" (col. A,B,C,D) (val. 1,2,3,4) and ON DUPLICATE KEY, which is found when vertreterkennzeichen matches param_agent_id, the current row is updated. So what does the composite index do? – frequent Jun 22 '12 at 01:09
@frequent: composite index is used to determine whether we need to insert another row, or to update existing one. As long as in the original question you had `WHERE ag.iln = param_loginID AND ag.verkaeufer = param_sellerILN` - this means you care of `iln` and `verkaeufer` to be unique. If you only used - `iln` there - then unique `iln` index would be enough – zerkms Jun 22 '12 at 01:57

Can I use MySQL ifnull with a select statement?

1 Answers1