Update table according to column quantity

Asked

Viewed 37 times

1

The registration table was not modeled correctly, but I need to do an UPDATE by SQL to perform this import, follow example:

Note that the children of Bomi Bulsara, need to stay in [nameDoDependent1] and the other in [nameDoDependente2], I know it is possible but I still have a great difficulty with SQL, could help me?

The table has up to four columns with the dependents and the table of dependents that needs to be imported need to identify the other dependents and be inserted in the next columns, example: if you have 3 dependents then insert into dependente1, dependente2, and dependente3**

Current table:

IDDoFuncionario - nomeDoFuncionario - nomeDoDependente1  - nomeDoDependente2  
12              - Joseph Jackson    - [nomeDoDependente] - [nomeDoDependente]  
13              - Bomi Bulsara      - [nomeDoDependente] - [****************]

Data to update "Current table":

Dependent table

IDDoFuncionario - nomeDoDependente
12              - Michael Jackson   
12              - Janet Jackson  
13              - Fred Mercury
mysql sql database

2 answers

1


Considering the "Current Table" = Table1 and "Table of dependent" = Table2

The following consultation makes the "transfer" of the data to be entered.

SELECT
    IDDoFuncionario,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1) AS nomeDoDependente1,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1 OFFSET 1) AS nomeDoDependente2,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1 OFFSET 2) AS nomeDoDependente3,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1 OFFSET 3) AS nomeDoDependente4
FROM Table1 AS T1

So just insert this SELECT in a UPDATE thus:

UPDATE Table1
LEFT JOIN (
  SELECT
    IDDoFuncionario,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1) AS nomeDoDependente1,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1 OFFSET 1) AS nomeDoDependente2,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1 OFFSET 2) AS nomeDoDependente3,
    (SELECT nomeDoDependente FROM Table2 WHERE T1.IDDoFuncionario = Table2.IDDoFuncionario LIMIT 1 OFFSET 3) AS nomeDoDependente4
  FROM Table1 AS T1
) AS q ON q.IDDoFuncionario = Table1.IDDoFuncionario
SET
  Table1.nomeDoDependente1 = q.nomeDoDependente1,
  Table1.nomeDoDependente2 = q.nomeDoDependente2,
  Table1.nomeDoDependente3 = q.nomeDoDependente3,
  Table1.nomeDoDependente4 = q.nomeDoDependente4;

  • Danilo, it didn’t work, I’ll put a line here because it has character limit: <pre><code> SELECT ID, (SELECT PAR00NOME FROM PEN_CADASTRO WHERE T1.ID = pen_cadastro_funcionarios_importados.ID LIMIT 1) AS nameDependente1, FROM PEN_CADASTRO AS T1 </code></pre>

  • @ownar rock.i did that fiddle and worked right there

  • Danilo, I discovered what I was missing is in the alias I didn’t specify, thank you very much man!!! Another thing where you learned bank?

  • Most of what I learned was building my databases and searching for answers to my questions on online forums like this.

  • Ahhh thank you very much man... thank you you helped pakas if you can’t imagine

0

Make an SQL of the type :

select IDDoFuncionario , nomeDoFuncionario , 1 codigoDoDependente , nomeDoDependente1 nomeDoDependente
from tabela_atual
union all
select IDDoFuncionario , nomeDoFuncionario , 2 codigoDoDependente , nomeDoDependente2 nomeDoDependente
from tabela_atual
union all
select IDDoFuncionario , nomeDoFuncionario , 3 codigoDoDependente , nomeDoDependente3 nomeDoDependente
from tabela_atual

this way the table is "unpivoteada" and it is easy to insert in a normalized table

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.