If the value is not empty then insert mysql
I have a table in mysql like (id is primary key):
id | name | age
1 | John | 46
2 | | 56
3 | Jane | 25
Now I want to update the name only if it is empty. If the value is not empty, it should duplicate the new ID string, but it should update the name.
I thought it could be done with an if statement, but it won'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 Spencer's answer, I made it work by using if in code. (However, I would still like to make only one 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()
source to share
If you do 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 the statement returns "true" because there is a row in your table where id = 2 and the 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)
)
source to share
IF
is not a valid MySQL statement (outside the context of the MySQL stored program).
You will need two operators to perform this operation.
Assuming a null-length string and NULL are the conditions you think are "empty" ...
To try to update a field name
, you can do something like this:
UPDATE table1 t
SET t.name = IF(CHAR_LENGTH(t.name)>0,t.name,'ally')
WHERE t.id = 3 ;
The expression IF
tests if the current value of the column is not empty. If it is not empty, the expression returns the current value of the column, resulting in a "not updated" value. If the column is empty, the expression returns "ally".
And you'll need a separate statement to try INSERT:
EDIT
This is wrong, not after a successful UPDATE
... existing line. Attempting an INSERT may need to be 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;
Strike>
We need a conditional test in the WHERE clause that prevents the row from being returned if we don't need to insert a row. We don't need to insert a row if the value is 'ally'
...
Usage CHAR_LENGTH >0
is a convenient test for a string that is not null and is not null. You can use a different test as you define "empty". Is one space in a column also "blank"?)
source to share