CODEWITHSUNDEEP
mysqlinsertexists
EOB
EOB

Reputation: 3085

MYSQL - insert into and a null value, how to avoid?

I have a SQL query, it looks like that:

insert into tableA(order_id, contractnotenumber, shipmentdate, comment) 
    values ((select entity_id from tableb where increment_id = '12345678'), 
    '', '1970-01-01', '');

Now, if the subquery ((select entity_id from tableb where increment_id = '12345678')) returns null, the insert does not work. What I want is an easy way to say that if the subquery returns null, dont do anything. I tried insert ignore, but this inserts the row with 0 instead of null, which works, but its not what I want. In SQL Server I used if not exists(select...), but this does not work with MYSQL.

Ideas?

Thanks!

Upvotes: 1

Views: 1285

Answers (2)

Louie Miranda
Louie Miranda

Reputation: 1159

You could also try mysql> insert ignore

Upvotes: 0

zerkms
zerkms

Reputation: 254886

insert into tableA(order_id, contractnotenumber, shipmentdate, comment)
select entity_id, '', '1970-01-01', '' from tableb where increment_id = '12345678'

This won't insert anything if the row isn't found in tableb

Upvotes: 1

Related Questions