0

Is there any way to migrate multiple columns data of table to only one column of other?

I need to migrate the data of table 1

subject          varchar(45) 
body             text
recipients       json
reason_id        int
status           tinyint(1)

to data (as an object) field in table 2

enter image description here

I've tried this

 INSERT INTO notice_templates (data)
    SELECT json_build_object
      ('subject'    , subject
      ,'body'       , body
      ,'status_id'  , status_id
      ,'reason_id'  , reason_id
      ,'recipients' , recipients
      ) AS data
    FROM notification;

But I get this error

#1305 - FUNCTION route_test.json_build_object does not exist

enter image description here

schlebe
  • 3,387
  • 5
  • 37
  • 50
Francisco Morales
  • 81
  • 3
  • 13
  • When you say _"migrate"_, that sounds to me as if you can get there by having a number of `Alter Table` statements together with a number of SQL `Update` statements. Did you try something like that already but it somehow failed? – Stefan Wuebbe Apr 22 '22 at 06:52
  • I've tryed this: INSERT INTO notice_templates (data) SELECT json_build_object('subject', subject, 'body', body, 'status_id', status_id, 'reason_id', reason_id, 'recipients', recipients) AS data FROM notification; but json_build_object does not exist – Francisco Morales Apr 22 '22 at 15:37
  • Does `MySql` have such a function? https://www.postgresql.org/docs/9.4/functions-json.html See also https://stackoverflow.com/questions/40930896/how-to-create-and-insert-a-json-object-using-mysql-queries – Stefan Wuebbe Apr 22 '22 at 20:37

0 Answers0