Saturday, March 10, 2012

MySQL insert a record by checking if record exist then add

I just figured out this trick in MySQL with the INSERT ... SELECT statement functionality.

How to do it, so I have a table named OFFERS and table named ACCOUNTS. Let's assumed that I have fields on these table, and it has OFFERS.CODE, OFFERS.USERID and ACCOUNTS.USERID.

What I want is, I want to check if ACCOUNTS.USERID exist in OFFERS table in just one SQL statement. To do this, just do

    
INSERT INTO OFFERS
(USERID, CODE)
    SELECT
        '22','XXHH22BBMM'
    FROM
        ACCOUNT a
    WHERE
    A.USERID='22' AND A.USERID NOT IN (
    SELECT OFFER.USERID FROM  OFFER
    WHERE OFFER.USERID='22'
)
    LIMIT 1


Now, the 22 is the USERID and the XXHH22BBMM values are used as my return value that I wanted actually to be inserted into the database. What exactly is happening here is that, this is equivalent to,



INSERT INTO OFFERS (USERID, CODE) VALUES
(22, 'XXHH22BBMM')




but the first SQL statement also checks if the code exist which compares the ACCOUNT table to OFFER table if such record in table OFFER with ACCOUNT.USERID = 22 exist.