'Using Select to Update based on the selected value

I have written a SQL query which gets me the data I want (Yes I know its not pretty),

SELECT characters.id AS ID, (SELECT CONCAT(
    '[{"license":"Weapon","issuedDate":"","issuedBy":"","issued":', 
    (SELECT user_licenses.`status` FROM user_licenses WHERE user_licenses.cid = ID AND user_licenses.`type` = "Weapon" LIMIT 1), 
    '},{"license":"Hunting","issuedDate":"","issuedBy":"","issued":', 
    (SELECT user_licenses.`status` FROM user_licenses WHERE user_licenses.cid = ID AND user_licenses.`type` = "Hunting" LIMIT 1), 
    '},{"license":"Business","issuedDate":"","issuedBy":"","issued":', 
    (SELECT user_licenses.`status` FROM user_licenses WHERE user_licenses.cid = ID AND user_licenses.`type` = "Business" LIMIT 1)
    , '},{"license":"Drivers","issuedDate":"","issuedBy":"","issued":', 
    (SELECT user_licenses.`status` FROM user_licenses WHERE user_licenses.cid = ID AND user_licenses.`type` = "Drivers" LIMIT 1), 
    '},{"license":"Fishing","issuedDate":"","issuedBy":"","issued":', 
    (SELECT user_licenses.`status` FROM user_licenses WHERE user_licenses.cid = ID AND user_licenses.`type` = "Fishing" LIMIT 1)
    , '},{"license":"Bar","issuedDate":"","issuedBy":"","issued":', 
    (SELECT user_licenses.`status` FROM user_licenses WHERE user_licenses.cid = ID AND user_licenses.`type` = "Bar" LIMIT 1)
    , '},{"license":"Pilot","issuedDate":"","issuedBy":"","issued":', 
    (SELECT user_licenses.`status` FROM user_licenses WHERE user_licenses.cid = ID AND user_licenses.`type` = "Pilot" LIMIT 1)
    , '}]')) AS Info FROM characters

which produces a table similar to

ID Info
1 [{"license":"Weapon","issuedDate":"","issuedBy":"","issued":1},{"license":"Hunting","issuedDate":"","issuedBy":"","issued":1},{"license":"Business","issuedDate":"","issuedBy":"","issued":1},{"license":"Drivers","issuedDate":"","issuedBy":"","issued":1},{"license":"Fishing","issuedDate":"","issuedBy":"","issued":1},{"license":"Bar","issuedDate":"","issuedBy":"","issued":0},{"license":"Pilot","issuedDate":"","issuedBy":"","issued":0}]
2 [{"license":"Weapon","issuedDate":"","issuedBy":"","issued":1},{"license":"Hunting","issuedDate":"","issuedBy":"","issued":0},{"license":"Business","issuedDate":"","issuedBy":"","issued":1},{"license":"Drivers","issuedDate":"","issuedBy":"","issued":1},{"license":"Fishing","issuedDate":"","issuedBy":"","issued":1},{"license":"Bar","issuedDate":"","issuedBy":"","issued":0},{"license":"Pilot","issuedDate":"","issuedBy":"","issued":0}]
3 [{"license":"Weapon","issuedDate":"","issuedBy":"","issued":0},{"license":"Hunting","issuedDate":"","issuedBy":"","issued":0},{"license":"Business","issuedDate":"","issuedBy":"","issued":0},{"license":"Drivers","issuedDate":"","issuedBy":"","issued":1},{"license":"Fishing","issuedDate":"","issuedBy":"","issued":1},{"license":"Bar","issuedDate":"","issuedBy":"","issued":0},{"license":"Pilot","issuedDate":"","issuedBy":"","issued":0}]

etc.

How can I then update the characters table so for every ID from the above table the license column gets set to the Info from the table?

sqlmariadb


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions