MYSQL function select sql

Question

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';

This is correct syntax for an INSERT INTO operation INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3); You are missing out the VALUES part — node_man
– node_man, Commented Oct 11, 2018 at 6:09
@pravinnavle you are wrong! OP is using Insert.. Select statement. Refer: dev.mysql.com/doc/refman/8.0/en/insert-select.html — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 11, 2018 at 6:11
@MadhurBhaiya thanks for pointing out my mistake. Got to learn something new about MySQL — node_man
– node_man, Commented Oct 11, 2018 at 6:16

Madhur Bhaiya · Accepted Answer · 2018-10-11 06:13:04Z

2

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';

answered Oct 11, 2018 at 6:13

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL function select sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related