CODEWITHSUNDEEP
mysqlsql
penguinnnnn
penguinnnnn

Reputation: 61

MYSQL function select sql

I already create a function called calculate_marks(user_id) and it run properly, but when i put in the insert statement, it will keep running in mysql and no respond, anyone know what is the problem?

INSERT INTO `marks`
(`marks_user_id`, `marks_addtime`, `marks_amount`
) 
SELECT `user_id`, UNIX_TIMESTAMP(),
    (SELECT calculate_marks(`user_id`))
FROM `user`
WHERE `user`.`user_status` = 'A';

It will return the result that i want if i run this

SELECT `user_id`, (SELECT calculate_marks(`user_id`))
FROM `user`
WHERE `user`.`user_status` = 'A';

Upvotes: 0

Views: 64

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You are trying a correlated subquery, without proper aliasing. You dont need to use subquery to calculate marks. Try the following instead:

INSERT INTO `marks`
(`marks_user_id`, `marks_addtime`, `marks_amount`
) 
SELECT `user_id`, UNIX_TIMESTAMP(), calculate_marks(`user_id`) 
FROM `user`
WHERE `user`.`user_status` = 'A';

Upvotes: 2

Related Questions