CODEWITHSUNDEEP
mysqlsql
Mayank Pandya
Mayank Pandya

Reputation: 1623

Call single function twice

I have a mysql function function1(n AS varchar) which returns some comma seperated string like str1, str2.

I can fire a select statement like

Select function1("ABC") from dual;

I want to insert output of this function in a table like comlumn1 contains str1 and column2 contains str2 I can do it with call function1("ABC") 2 times and split output by comma and store each part in particular column. but is there any way to insert values to table by just calling function1 only once.

Upvotes: 2

Views: 187

Answers (1)

Arth
Arth

Reputation: 13110

I think you can do:

   UPDATE table_name t
     JOIN (
       SELECT function1("ABC") val
/*       FROM dual /* Not sure if you need this */
          ) f
      SET t.column1 = SUBSTRING_INDEX(f.val,', ', 1),
          t.column2 = SUBSTRING_INDEX(f.val,', ',-1)
/*  WHERE ... */

I'm pretty sure that'll only call the function once.

Upvotes: 2

Related Questions