MySQL Don't INSERT If NULL Fields

Question

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);

GolezTrol · Accepted Answer · 2015-02-08 23:04:09Z

2

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.

edited Feb 8, 2015 at 23:04

answered Feb 8, 2015 at 21:50

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Preston Connors Over a year ago

I had to use HAVING inventory.nick IS NOT NULL instead of WHERE inventory.nick IS NOT NULL. Using WHERE still yielded NULL results for some reason.

GolezTrol Over a year ago

Ah, I actually didn't notice the aggregation (SUM) in the query. Normally you would need a GROUP BY clause as well, but MySQL allows you to do it without. Anyway, even with an implicit group by, you might need to use HAVING.

Beri · Accepted Answer · 2015-02-08 21:51:25Z

0

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;

answered Feb 8, 2015 at 21:51

Beri

11.6k4 gold badges38 silver badges59 bronze badges

Collectives™ on Stack Overflow

MySQL Don't INSERT If NULL Fields

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related