Jump to content


Photo

MySQL Insert with SELECT statement. If result of select is NULL, then a value else result

mysql insert with select conditional mysql select subquery mysql if statement

Best Answer gizmola, 06 April 2013 - 02:40 PM

Try IFNULL().

SELECT IFNULL(seq_num, 1) FROM table ...
Go to the full post


  • Please log in to reply
2 replies to this topic

#1 Jagand

Jagand

    Advanced Member

  • Members
  • PipPipPip
  • 39 posts

Posted 06 April 2013 - 02:25 PM

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
 

 



#2 gizmola

gizmola

    Advanced Member

  • Administrators
  • 3,968 posts
  • LocationLos Angeles, CA USA

Posted 06 April 2013 - 02:40 PM   Best Answer

Try IFNULL().
SELECT IFNULL(seq_num, 1) FROM table ...


#3 Jagand

Jagand

    Advanced Member

  • Members
  • PipPipPip
  • 39 posts

Posted 06 April 2013 - 03:35 PM

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com