CODEWITHSUNDEEP
mysql
Preston Connors
Preston Connors

Reputation: 407

MySQL Don't INSERT If NULL Fields

How would I NOT do any INSERTs for the following statement if inventory.nick was NULL? Thank you in advance!

INSERT INTO gold_log (nick, amount, stream_online, modification_type, dt) 
    SELECT inventory.nick, SUM(inventory.stat_2*.01), False, 12, NOW()
    FROM inventory
        INNER JOIN equipment_shoes ON
        inventory.id=equipment_shoes.id
        AND inventory.item=8
        INNER JOIN gold_log ON 
        inventory.nick=gold_log.nick
        AND gold_log.dt > FROM_UNIXTIME(1423421534.32);

Upvotes: 1

Views: 3894

Answers (2)

GolezTrol
GolezTrol

Reputation: 116190

Add that condition in the where clause? 

INSERT INTO gold_log (nick, amount, stream_online, modification_type, dt) 
    SELECT inventory.nick, SUM(inventory.stat_2*.01), False, 12, NOW()
    FROM inventory
        INNER JOIN equipment_shoes ON
        inventory.id=equipment_shoes.id
        AND inventory.item=8
        INNER JOIN gold_log ON 
        inventory.nick=gold_log.nick
        AND gold_log.dt > FROM_UNIXTIME(1423421534.32);
    HAVING
        inventory.nick IS NOT NULL

(Changed WHERE to HAVING because of implicit grouping caused by the SUM)

Additionally, you can make the column itself not nullable:

ALTER TABLE inventory MODIFY COLUMN nick VARCHAR(200) NOT NULL;

But that alone won't solve the issue. If the column is not nullable, and the column has a default value, all new rows get this default value if nick was empty in the query. If it doesn't have a default value, then the insert will fail completely, including those rows that do have a nick filled in. So the change to the insert statement itself is the most important: it will just skip those rows that don't have a nick.

Upvotes: 2

Beri
Beri

Reputation: 11620

Simply add IS NOT NULL criteria:

INSERT INTO gold_log (nick, amount, stream_online, modification_type, dt) 
    SELECT inventory.nick, SUM(inventory.stat_2*.01), False, 12, NOW()
    FROM inventory
        INNER JOIN equipment_shoes ON
        inventory.id=equipment_shoes.id
        AND inventory.item=8
        INNER JOIN gold_log ON 
        inventory.nick=gold_log.nick
        AND gold_log.dt > FROM_UNIXTIME(1423421534.32)
        WHERE inventory.nick IS NOT NULL;

Upvotes: 0

Related Questions