Does anyone have any suggestions on which way to go to perform an update on a table. Considering a query performed in DB with related tables.
The code I developed already has read(table), insert and delete working fine. But the update I don't know which way to go.
Below is an example of the code already created... And which objective I need to follow.
Fluxo:
[{"id":"f99afabc09e9ddd6","type":"debug","z":"ea86445f05051d5d","name":"debug 11","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1100,"y":700,"wires":[]},{"id":"11a36b7f7efcdc6c","type":"change","z":"ea86445f05051d5d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"5","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":840,"wires":[["dc4025e64d7b0a9f","f99afabc09e9ddd6"]]},{"id":"58c33f8480d4d339","type":"inject","z":"ea86445f05051d5d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":840,"wires":[["06efef27d001a1c2"]]},{"id":"cffcd7f5b4032649","type":"debug","z":"ea86445f05051d5d","name":"debug 12","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1100,"y":860,"wires":[]},{"id":"66fbc1ee45a2e531","type":"change","z":"ea86445f05051d5d","name":"bck - change","rules":[{"t":"set","p":"options","pt":"msg","to":"$$.payload.$merge([{$.Equipe: {\"nEquipe\":$.nEquipe, \"Equipe\":$.Equipe}}])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1330,"y":980,"wires":[[]]},{"id":"de76bac7029e67af","type":"group","z":"ea86445f05051d5d","name":"View Table","style":{"stroke":"#c8e7a7","label":true,"fill":"#e3f3d3"},"nodes":["acd4288657e0e1e9","dcf0460394397d3b","a87fae56abb17d99","6c44656e0b3661a7","c16f38761aea6e5e","c4d04057b8e30b1f","d19c329f17f15b9b","ca339f798545c65c"],"x":34,"y":19,"w":482,"h":222},{"id":"4639c7fae2510b5a","type":"subflow","name":"exec_BD","info":"","category":"","in":[{"x":80,"y":100,"wires":[{"id":"8cc0b55b7d0743c0"}]}],"out":[{"x":1020,"y":100,"wires":[{"id":"4135b832b386a610","port":0}]}],"env":[],"meta":{},"color":"#DDAA99","status":{"x":1020,"y":200,"wires":[{"id":"84933fc7168ae2b4","port":0}]}},{"id":"8cc0b55b7d0743c0","type":"change","z":"4639c7fae2510b5a","name":"copy flow/global to msg","rules":[{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"payload.ip","pt":"msg","to":"#:(persistent)::IP_vGlobalBD","tot":"global"},{"t":"set","p":"payload.porta","pt":"msg","to":"#:(persistent)::Porta_vGlobalBD","tot":"global"},{"t":"set","p":"payload.usuario","pt":"msg","to":"#:(persistent)::Usuario_vGlobalBD","tot":"global"},{"t":"set","p":"payload.senha","pt":"msg","to":"#:(persistent)::Senha_vGlobalBD","tot":"global"},{"t":"set","p":"payload.nomeBD","pt":"msg","to":"#:(persistent)::NomeBD_vGlobalBD","tot":"global"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":100,"wires":[["effa65099dd2a525"]]},{"id":"4135b832b386a610","type":"mysql2","z":"4639c7fae2510b5a","name":"","server":"","bind":"","topic":"","x":760,"y":100,"wires":[[]]},{"id":"effa65099dd2a525","type":"function","z":"4639c7fae2510b5a","name":"prep_MQTT_Conn","func":"\nconst ip = msg.payload.ip\nconst porta = msg.payload.porta\nconst usuario = msg.payload.usuario\nconst senha = msg.payload.senha\nconst nomeBD = msg.payload.nomeBD\n\nmsg.server = {\n \"host\": ip,\n \"port\": porta,\n \"username\": usuario,\n \"password\": senha,\n \"db\": nomeBD\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":100,"wires":[["4135b832b386a610"]]},{"id":"eb96b8b79efaea17","type":"change","z":"4639c7fae2510b5a","name":"","rules":[{"t":"set","p":"server","pt":"msg","to":"{'host': $globalContext('IP_vGlobalBD'),\t 'port': $globalContext('Porta_vGlobalBD'),\t 'username': $globalContext('Usuario_vGlobalBD'),\t 'password': $globalContext('Senha_vGlobalBD'),\t 'bd': \"TelemetriaEnervision\"\t }","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":260,"wires":[[]]},{"id":"4a50ea77c5d0ed5b","type":"comment","z":"4639c7fae2510b5a","name":"Quem sabe futuro usar esse Change","info":"Não consegui fazer funcionar com o mysql2, ele diz que o nome do banco de dados não está selecionado.","x":320,"y":220,"wires":[]},{"id":"84933fc7168ae2b4","type":"status","z":"4639c7fae2510b5a","name":"","scope":null,"x":840,"y":200,"wires":[[]]},{"id":"1a924288b2bd59bd","type":"comment","z":"4639c7fae2510b5a","name":"Variaveis Globais, com as informações do BD","info":"As informações sobre a conexão ao Banco de dados estão nas variaveis globais do banco de dados.","x":350,"y":60,"wires":[]},{"id":"acd4288657e0e1e9","type":"inject","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"4","topic":"","payload":"","payloadType":"date","x":150,"y":100,"wires":[["dcf0460394397d3b","ca339f798545c65c"]]},{"id":"dcf0460394397d3b","type":"function","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"f_SQL","func":"\n//msg.topic = \"Select Mergulhador.*, CONCAT('<a target=_blank href=apagarMergulhador', '', '>Apagar</a>') as Apagar from Mergulhador\"\n//msg.topic = \"Select Mergulhador.*, CONCAT('<a target=framename href=apagarMergulhador?nMergulhador=', nMergulhador, '>Apagar</a>') as Apagar from Mergulhador\"\n//msg.topic = \"Select Mergulhador.*, CONCAT('<a target=xApagar href=apagarMergulhador?nMergulhador=', nMergulhador, '>Apagar</a>') as Apagar from Mergulhador\"\n//msg.topic = \"SELECT Mergulhador.nMergulhador, Mergulhador.Mergulhador,Equipe.Equipe, CONCAT('<a target=xApagar href=apagarMergulhador?nMergulhador=', nMergulhador, '>Apagar</a>') as Apagar FROM Equipe LEFT JOIN Mergulhador ON Equipe.nEquipe = Mergulhador.nEquipe AND Mergulhador.nEquipe = Equipe.nEquipe\";\n\nlet sql = \"SELECT \"\nsql = sql + \"Mergulhador.nMergulhador,\"\nsql = sql + \" Mergulhador.Mergulhador,\"\nsql = sql + \" Equipe.Equipe,\"\nsql = sql + \" CONCAT('<a target=xApagar href=apagarMergulhador?nMergulhador=', nMergulhador, '>Apagar</a>') as Apagar, \"\nsql = sql + \" CONCAT('<a target=xApagar href=modificarMergulhador?nMergulhador=', nMergulhador, '>Modificar</a>') as Modificar \"\n\nsql = sql + \"FROM Mergulhador LEFT JOIN Equipe ON Equipe.nEquipe = Mergulhador.nEquipe AND Mergulhador.nEquipe = Equipe.nEquipe\"\n\nmsg.topic = sql\n\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":80,"wires":[["d19c329f17f15b9b"]]},{"id":"a87fae56abb17d99","type":"ui_table","z":"ea86445f05051d5d","g":"de76bac7029e67af","group":"972474baadf22d9f","name":"","order":10,"width":17,"height":12,"columns":[{"field":"Mergulhador","title":"Mergulhador","width":"40%","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Equipe","title":"Equipe","width":"22%","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Apagar","title":"Apagar","width":"15%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"Modificar","title":"Modificar","width":"15%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}}],"outputs":1,"cts":true,"x":430,"y":200,"wires":[[]]},{"id":"6c44656e0b3661a7","type":"link in","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"Mostra_Tabela","links":["f7cecf442f2f57d9","c7578da38b131f5c","af7bf3fc03fb7e93"],"x":275,"y":120,"wires":[["dcf0460394397d3b"]]},{"id":"c16f38761aea6e5e","type":"ui_template","z":"ea86445f05051d5d","g":"de76bac7029e67af","group":"972474baadf22d9f","name":"","order":35,"width":3,"height":1,"format":"<iframe name=xApagar style=\"display: none;\"></iframe>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":140,"y":200,"wires":[[]]},{"id":"c4d04057b8e30b1f","type":"ui_button","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"Atualizar","group":"972474baadf22d9f","order":6,"width":1,"height":1,"passthru":false,"label":"<i class=\"fa fa-refresh fa-2x\" aria-hidden=\"true\"></i>","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":140,"y":60,"wires":[["dcf0460394397d3b"]]},{"id":"d19c329f17f15b9b","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"","x":430,"y":140,"wires":[["a87fae56abb17d99"]]},{"id":"ca339f798545c65c","type":"link out","z":"ea86445f05051d5d","g":"de76bac7029e67af","name":"link out 26","mode":"link","links":["c605872d817b45c7"],"x":275,"y":160,"wires":[]},{"id":"972474baadf22d9f","type":"ui_group","name":"","tab":"657fd39515811ee7","order":1,"disp":true,"width":19,"collapse":false,"className":""},{"id":"657fd39515811ee7","type":"ui_tab","name":"Mergulhadores","icon":"directions_walk","order":5,"disabled":false,"hidden":false},{"id":"059f04f0839c5d5a","type":"group","z":"ea86445f05051d5d","name":"Insert Row","style":{"label":true,"stroke":"#0070c0","fill":"#bfdbef"},"nodes":["8757bcd1fc3cb715","8a4d2e4459a0b06e","acce5b075c104af8","167c0a8b5cd79911","aaaf375cf8695f44","ed7226ddb2937935","f7cecf442f2f57d9","376305b879dfdf48","c605872d817b45c7","973ee8ceb21e35b7","f53971e4134c376d","06efef27d001a1c2","dc4025e64d7b0a9f","3ca031c663b3cb8f"],"x":34,"y":339,"w":1152,"h":222},{"id":"8757bcd1fc3cb715","type":"ui_text_input","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","label":"Mergulhador","tooltip":"","group":"972474baadf22d9f","order":3,"width":5,"height":1,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":910,"y":380,"wires":[["167c0a8b5cd79911"]]},{"id":"8a4d2e4459a0b06e","type":"ui_button","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","group":"972474baadf22d9f","order":5,"width":2,"height":1,"passthru":false,"label":"Inserir","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":380,"wires":[["aaaf375cf8695f44"]]},{"id":"acce5b075c104af8","type":"function","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"f_SQL","func":"let vMergulhador = msg.payload\nlet nEquipe = msg.nEquipe\n\n\nmsg.topic = \"Insert INTO Mergulhador (Mergulhador, nEquipe) Values ('\" + vMergulhador + \"', \" + nEquipe + \")\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":440,"wires":[["376305b879dfdf48"]]},{"id":"167c0a8b5cd79911","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","rules":[{"t":"set","p":"vMergulhador","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":940,"y":440,"wires":[[]]},{"id":"aaaf375cf8695f44","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"vMergulhador","tot":"flow"},{"t":"set","p":"nEquipe","pt":"msg","to":"vnEquipe","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":380,"wires":[["ed7226ddb2937935","acce5b075c104af8"]]},{"id":"ed7226ddb2937935","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":380,"wires":[["8757bcd1fc3cb715"]]},{"id":"f7cecf442f2f57d9","type":"link out","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"link out 1","mode":"link","links":["6c44656e0b3661a7"],"x":725,"y":440,"wires":[]},{"id":"376305b879dfdf48","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","x":600,"y":440,"wires":[["f7cecf442f2f57d9"]]},{"id":"c605872d817b45c7","type":"link in","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"link in 18","links":["ca339f798545c65c"],"x":85,"y":520,"wires":[["973ee8ceb21e35b7"]]},{"id":"973ee8ceb21e35b7","type":"function","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"fSQL","func":"msg.topic = \"SELECT * FROM Equipe;\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":520,"wires":[["f53971e4134c376d"]]},{"id":"f53971e4134c376d","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","x":440,"y":520,"wires":[["06efef27d001a1c2"]]},{"id":"06efef27d001a1c2","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"change","rules":[{"t":"set","p":"options","pt":"msg","to":"$$.payload.$merge([{$.Equipe: {\"nEquipe\":$.nEquipe, \"Equipe\":$.Equipe}}])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":520,"wires":[["dc4025e64d7b0a9f","cffcd7f5b4032649"]]},{"id":"dc4025e64d7b0a9f","type":"ui_dropdown","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"cbo_Equipe","label":"Equipe","tooltip":"","place":"Escolha","group":"972474baadf22d9f","order":4,"width":9,"height":1,"passthru":true,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":830,"y":520,"wires":[["3ca031c663b3cb8f","f99afabc09e9ddd6"]]},{"id":"3ca031c663b3cb8f","type":"change","z":"ea86445f05051d5d","g":"059f04f0839c5d5a","name":"","rules":[{"t":"set","p":"vnEquipe","pt":"flow","to":"payload.nEquipe","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1070,"y":520,"wires":[[]]},{"id":"b5f89aababd0dc2b","type":"group","z":"ea86445f05051d5d","name":"Delete Row","style":{"stroke":"#6f2fa0","fill":"#b797cf","label":true},"nodes":["5a6a5fd24ca74cfa","965b9fcea32fdc32","f6c496041602c3c0","34d8ce1f4f8b96b1","c7578da38b131f5c","2a1da7bed5e5e333","6047941a4bcb42c6","69f16a8e5cb36462"],"x":754,"y":19,"w":572,"h":202},{"id":"5a6a5fd24ca74cfa","type":"ui_toast","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","position":"dialog","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"SIM","cancel":"NÃO","raw":false,"className":"","topic":"","name":"","x":1010,"y":100,"wires":[["34d8ce1f4f8b96b1"]]},{"id":"965b9fcea32fdc32","type":"change","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"Deseja apagar esse registro ?","tot":"str"},{"t":"set","p":"vnMergulhador","pt":"flow","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"Esse registro será apagado definitivamente","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1020,"y":60,"wires":[["5a6a5fd24ca74cfa"]]},{"id":"f6c496041602c3c0","type":"change","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"vnMergulhador.nMergulhador","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":1020,"y":180,"wires":[["6047941a4bcb42c6"]]},{"id":"34d8ce1f4f8b96b1","type":"switch","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"SIM","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":990,"y":140,"wires":[["f6c496041602c3c0"]]},{"id":"c7578da38b131f5c","type":"link out","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"link out 2","mode":"link","links":["6c44656e0b3661a7"],"x":1285,"y":160,"wires":[]},{"id":"2a1da7bed5e5e333","type":"http in","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"Apagar","url":"/ui/apagarMergulhador","method":"get","upload":false,"swaggerDoc":"","x":830,"y":60,"wires":[["965b9fcea32fdc32"]]},{"id":"6047941a4bcb42c6","type":"function","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"f_SQL","func":"let vnMergulhador = msg.payload\n\nmsg.topic = \"DELETE FROM Mergulhador WHERE nMergulhador = \" + vnMergulhador ;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1230,"y":60,"wires":[["69f16a8e5cb36462"]]},{"id":"69f16a8e5cb36462","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"b5f89aababd0dc2b","name":"","x":1230,"y":100,"wires":[["c7578da38b131f5c"]]},{"id":"7ce02d9c4803025d","type":"group","z":"ea86445f05051d5d","name":"Delete Row","style":{"stroke":"#6f2fa0","fill":"#b797cf","label":true},"nodes":["af7bf3fc03fb7e93","2b15dec6311c7133","2565aba27e676ca7","d69a495595da61b6","36fe10cfb5cc1aa5","fb87d887dab4684e","0d5b1f2fdf096be4"],"x":44,"y":599,"w":852,"h":202},{"id":"af7bf3fc03fb7e93","type":"link out","z":"ea86445f05051d5d","g":"7ce02d9c4803025d","name":"link out 30","mode":"link","links":["6c44656e0b3661a7"],"x":855,"y":760,"wires":[]},{"id":"2b15dec6311c7133","type":"http in","z":"ea86445f05051d5d","g":"7ce02d9c4803025d","name":"Modificar","url":"/ui/modificarMergulhador","method":"get","upload":false,"swaggerDoc":"","x":130,"y":720,"wires":[["0d5b1f2fdf096be4"]]},{"id":"2565aba27e676ca7","type":"comment","z":"ea86445f05051d5d","g":"7ce02d9c4803025d","name":"Existe um fluxo para campo modificado","info":"","x":600,"y":640,"wires":[]},{"id":"d69a495595da61b6","type":"change","z":"ea86445f05051d5d","g":"7ce02d9c4803025d","name":"Mergulhador","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].Mergulhador","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":700,"y":760,"wires":[["8757bcd1fc3cb715"]]},{"id":"36fe10cfb5cc1aa5","type":"change","z":"ea86445f05051d5d","g":"7ce02d9c4803025d","name":"nEquipe","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].nEquipe","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":690,"y":720,"wires":[["dc4025e64d7b0a9f"]]},{"id":"fb87d887dab4684e","type":"subflow:4639c7fae2510b5a","z":"ea86445f05051d5d","g":"7ce02d9c4803025d","name":"","x":340,"y":740,"wires":[["36fe10cfb5cc1aa5","d69a495595da61b6"]]},{"id":"0d5b1f2fdf096be4","type":"function","z":"ea86445f05051d5d","g":"7ce02d9c4803025d","name":"f_SQL","func":"\nmsg.topic = \"Select * from Mergulhador Where nMergulhador = \" + msg.payload.nMergulhador ;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":350,"y":680,"wires":[["fb87d887dab4684e"]]}]
