Update table according to column quantity

Question

Update table according to column quantity

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

The registration table was not modeled correctly, however I need to do an UPDATE per SQL to perform this import, following example:

See that the children of Bomi Bulsara, need to be in [dependent_name1] and the other in [dependent_name2], I know it is possible but I still have a great difficulty with SQL, could you help me?

The table has up to four columns with the dependent and the dependent table that needs to be imported need to identify the other dependents and be inserted in the next columns, eg if you have 3 dependents then irar insert in dependent1, dependent2, and dependent3 **

Current table:

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

Data to update the "Current table":

Dependent table

IDDoFuncionario - nomeDoDependente
12              - Michael Jackson   
12              - Janet Jackson  
13              - Fred Mercury

mysql sql

asked by anonymous 24.07.2017 / 15:11

2 answers

0

Make an SQL of 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 "unpivoted" and it is easy to insert into a normalized table

24.07.2017 / 16:27

How to make a clock / how to run in the background? Tapply function - arguments must have the same length

score 1 · Accepted Answer

Considering "Current table" = Table1 and "Dependent table" = Table2

The following query causes the "transposition" 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 into a UPDATE like this:

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;