Posts

Showing posts from March 4, 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