CODEWITHSUNDEEP
mysql
copyka22
copyka22

Reputation: 21

mysql select function running twice

I want to use a function inside a select query. This is my test database.

When I use a select, get a result, but the function is running twice... why? I want to insert one row in this case.

Help, please!

create database db1_test;
use db1_test;

create table t1(
id int(11) primary key auto_increment,
t1col1 varchar(20) not null,
t1col2 int(1) not null
);

create table t2(
id int(11) primary key auto_increment,
t2col1 int(11) not null,
t2col2 datetime
);

insert into t1 (t1col1, t1col2) values ('row1', 1);
insert into t1 (t1col1, t1col2) values ('row2', 0);
insert into t1 (t1col1, t1col2) values ('row4', 1);

drop function if exists func1;

DELIMITER $$

CREATE FUNCTION func1(id int) RETURNS datetime
BEGIN
insert into t2 (`t2col1`, `t2col2`) values (id, now());
RETURN now();
END $$

DELIMITER ;

TEST :

SELECT id, t1col2, func1(id) FROM `t1` WHERE 1 and `t1`.`t1col1`='row1';
SELECT id, t1col2, func1(id) FROM `t1` WHERE 1 and `t1`.`t1col1`='row2';
SELECT id, t1col2, func1(id) FROM `t1` WHERE 1 and `t1`.`t1col1`='row4';

Upvotes: 0

Views: 111

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

Functions are generally used to store reuseable code which returns a scalar value from a calculation or transformation. I think you could

insert into t2(t2col1,t2col2)
SELECT id, now()
FROM `t1` 
WHERE `t1`.`t1col1`='row1';

Upvotes: 1

Related Questions