CODEWITHSUNDEEP
mysql
user3605780
user3605780

Reputation: 7072

if value is not empty, then insert mysql

I have a table in mysql like this (the id is primary key):

 id | name | age
 1 |  John | 46
 2 |       | 56
 3 | Jane  | 25

Now I want to update the name only if this is empty. If the value is not empty it should duplicate the row with a new id else it should update the name.

I thought it could be done with an if-statement but it doesn't work.

if((select `name` from `table1` where `id` = 3) = '',
update `table1` set `name`='ally' where `id` = 3, 
insert into `table1` (`id`,`name`,`age`) values 
(4, 'ally', select `age` from `table1` where `id` = 3))

EDIT:

With Spencers answer I made it working using an if in the code. (However I would still like a way to do just a single mysql query).

db.set_database('database1')
cursor = db.cursor()

query = "select IF(CHAR_LENGTH(name)>0,1,0) from table1 where id = {0}".format(id)
cursor.execute(query)
val1 = cursor.fetchone()

if val1[0]:
    query = "INSERT INTO `table1` (`id`,`name`,`age`) SELECT {0},{1},`age` FROM `table1` WHERE `id` = {2}".format(new_id, name, id)
    cursor.execute(query)
else:
    query = "update `table1` set `name` = '{0}' where `id` = {1}".format(name, id)
    cursor.execute(query)

db.commit()

Upvotes: 3

Views: 5225

Answers (2)

spencer7593
spencer7593

Reputation: 108390

IF is not a valid MySQL statement (outside the context of a MySQL stored program).

To perform this operation, you'll need two statements.

Assuming that a zero length string and a NULL value are both conditions you'd consider as "empty"...

To conditionally attempt an update of the name field, you could do something like this:

UPDATE table1 t
   SET t.name = IF(CHAR_LENGTH(t.name)>0,t.name,'ally')
 WHERE t.id = 3 ;

The IF expression tests whether the current value of the column is "not empty". If it's not empty, the expression returns the current value of the column, resulting in "no update" to the value. If the column is empty, then the expression returns 'ally'.

And you'd need a separate statement to attempt an INSERT:

EDIT

This isn't right, not after a successful UPDATE... of the existing row. The attempt to INSERT might need to run first, 

INSERT INTO table1 (id,name,age)
SELECT 4 AS id, 'ally' AS name, t.age
  FROM table1 t
 WHERE t.id = 3
   AND CHAR_LENGTH(t.name)>0;

We need a conditional test in the WHERE clause that prevents a row from being returned if we don't need to insert a row. We don't need to insert a row if the value 'ally' ...

The use of CHAR_LENGTH >0 is a convenient test for string that is not null and is not zero length. You could use different test, for however you define "empty". Is a single space in the column also considered "empty"?)

Upvotes: 1

EntGriff
EntGriff

Reputation: 875

If you make like this : 

select t.*, 
if( 
    EXISTS(select n.name from table1 n where n.id = 2 and NULLIF(n.name, '') is  null) , 
    'true', 
    'false' 
  ) from table1 t

if statement returns "true", becouse in your table exist row where id =2 and name is empty.

like this example, You can edit your query : 

if(
     EXISTS(select n.name from table1 n where n.id = 3 and NULLIF(n.name, '') is  null),
     update `table1` set `name`='ally' where `id` = 3, 
    insert into `table1` (`id`,`name`,`age`) values 
  (4, 'ally', select `age` from `table1` where `id` = 3)
)

Upvotes: 1

Related Questions