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
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,
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.
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.
Comments