Jagand Posted April 6, 2013 Share Posted April 6, 2013 I am trying to write a MySQL INSERT statement which has a SELECT query. Select query can result in NULL. If result is NULL, I want to 1 to be the value for the field. If result is not NULL, I want retrieved value. Below is the MySQL I have coded but it seems wrong. What would be the right statement INSERT INTO table ( primary_id ,sequence_number ,text ,text2 ,text3 )select 30 ,((SELECT MAX(sequence_number) + 1 FROM table WHERE primary_id = 30) AS seq_num IF (seq_num = NULL,1,seq_num)) ,'text' ,'text2' ,'text3 Link to comment https://forums.phpfreaks.com/topic/276621-mysql-insert-with-select-statement-if-result-of-select-is-null-then-a-value-else-result/ Share on other sites More sharing options...
gizmola Posted April 6, 2013 Share Posted April 6, 2013 Try IFNULL(). SELECT IFNULL(seq_num, 1) FROM table ... Link to comment https://forums.phpfreaks.com/topic/276621-mysql-insert-with-select-statement-if-result-of-select-is-null-then-a-value-else-result/#findComment-1423315 Share on other sites More sharing options...
Jagand Posted April 6, 2013 Author Share Posted April 6, 2013 Thank you. That worked. So, the script that worked is below, for other's reference. INSERT INTO table ( primary_id ,sequence_number ,text ,text2 ,text3 )select 30 ,(SELECT ifnull ((MAX(sequence_number) + 1), 1) FROM table WHERE primary_id = 30) ,'text' ,'text2' ,'text3 Link to comment https://forums.phpfreaks.com/topic/276621-mysql-insert-with-select-statement-if-result-of-select-is-null-then-a-value-else-result/#findComment-1423323 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.