Início / dba / Perguntas / 45642
Accepted
Jharwood
Asked: 2013-07-04 01:46:55 +0800 CST

Atualização em massa de todas as colunas

  • 772

Eu estou querendo saber se o PostgreSQL tem uma consulta de atualização um pouco como sua sintaxe de valores de inserção.

Eu tenho um conjunto atualizado de dados neste formulário atualmente:

INSERT INTO bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID)
VALUES(109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null);

e quero atualizar as linhas que já estão lá para os novos dados, estou procurando algo como o seguinte, onde posso atualizar todas as linhas sem me repetir:

UPDATE ON ID
bought_in_control_panel(ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) 
VALUES(
        (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
        (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
);

O acima verificaria os IDvalores correspondentes e atualizaria as correspondências com os novos dados.

Observação: estou realmente procurando evitar o mapeamento manual de todos os nomes das colunas. O PostgreSQL já conhece minhas colunas e já as mapeei nos dados. Por que fazer isso novamente em um formato mais longo?

postgresql update

3 respostas

  1. Best Answer

    Se você deseja apenas atualizar os dados, não sei para que INSERTserve a declaração na sua pergunta.

    Se você deseja apenas atualizar várias linhas com uma única instrução, pode estar procurando por isso:

    with update_values (ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) as 
(
  VALUES
     (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
     (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
)
update bought_in_control_panel
   set parent_id = ud.parent_id, 
       bought_in_form_type_id = ud.bought_in_form_type_id,
       ....
from update_values ud
where ud.id = bought_in_control_panel.id;
    • 8

  2. Na verdade, existe uma variante de sintaxe mais curta para isso, disponível desde o Postgres 8.2 (lançado em 2006!). As notas de lançamento:

    • Permitir UPDATEdefinir várias colunas com uma lista de valores (Susanne Ebrecht)

    Este é basicamente um atalho para atribuir as colunas e valores em pares. A sintaxe é UPDATE tab SET (column, ...) = (val, ...).

    Aplicado à sua consulta:

    UPDATE bought_in_control_panel b
SET   (  parent_id,   bought_in_form_type_id,   priority,   name,   heading,   description,   icon,   bought_in_control_panel_file_id) 
    = (v.parent_id, v.bought_in_form_type_id, v.priority, v.name, v.heading, v.description, v.icon, v.bought_in_control_panel_file_id) 
FROM (
   VALUES
      (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null)
    , (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
   ) AS v(id,parent_id,bought_in_form_type_id,priority,name,heading
         ,description,icon,bought_in_control_panel_file_id)
WHERE  b.id = v.id;

    Você ainda tem que listar todas as colunas, mas você pode cortar alguns ruídos e é mais fácil montar uma lista, copiá-la e prefixar o alias da tabela de origem. Você também pode fazer isso dinamicamente. Veja abaixo.

    Também usando uma subconsulta, que funciona tão bem quanto um CTE aqui, mas com um pouco menos de sobrecarga (desempenho).

    Você pode até misturar listas e colunas únicas livremente , se quiser. Nenhuma diferença no resultado, mas pode ser útil para formatação ou, mais importante, para comandos gerados dinamicamente:

    UPDATE bought_in_control_panel b
SET    parent_id = v.parent_id
    , (  bought_in_form_type_id,   priority,   name,   heading) 
    = (v.bought_in_form_type_id, v.priority, v.name, v.heading) 
    , (  description,   icon,   bought_in_control_panel_file_id) 
    = (v.description, v.icon, v.bought_in_control_panel_file_id) 
FROM (
   VALUES
      (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null)
    , (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
   ) AS v(id,parent_id,bought_in_form_type_id,priority,name,heading
         ,description,icon,bought_in_control_panel_file_id)
WHERE  b.id = v.id;

    Está tudo no manual se você olhar de perto.

    Falando em SQL dinâmico , esta resposta relacionada no SO demonstra como buscar a lista de colunas do catálogo do sistema pg_attributee executar tudo em uma função:

    • 4

  3. IIRC postgres não tem MERGEou similar (embora pareça estar sendo falado para versões posteriores: http://wiki.postgresql.org/wiki/SQL_MERGE ).

    Se você sabe que todas as linhas em questão existem, você pode fazer algo como:

    UPDATE bicp 
SET    PARENT_ID                       = newdata.PARENT_ID                       
     , BOUGHT_IN_FORM_TYPE_ID          = newdata.BOUGHT_IN_FORM_TYPE_ID          
     , PRIORITY                        = newdata.PRIORITY                        
     , NAME                            = newdata.NAME                            
     , HEADING                         = newdata.HEADING                         
     , DESCRIPTION                     = newdata.DESCRIPTION                     
     , ICON                            = newdata.ICON                            
     , BOUGHT_IN_CONTROL_PANEL_FILE_ID = newdata.BOUGHT_IN_CONTROL_PANEL_FILE_ID 
FROM   (    SELECT ID                              = 109
                 , PARENT_ID                       = 1
                 , BOUGHT_IN_FORM_TYPE_ID          = 28
                 , PRIORITY                        = 100
                 , NAME                            = 'Tooling'
                 , HEADING                         = 'Tooling'
                 , DESCRIPTION                     = 'Enter you Machine Tools Here'
                 , ICON                            = 'tooling.png'
                 , BOUGHT_IN_CONTROL_PANEL_FILE_ID = CAST(NULL AS INT)
           UNION ALL SELECT 1,0,1,200,'Bought In','Bought In','','boughtin.png',null
       ) AS newdata 
JOIN   bought_in_control_panel AS bicp ON newdata.ID = bicp.ID

    adicionando um extra UNIONpara cada linha subsequente que precisa de atualização, embora sejamos bastante prolixos para apenas algumas atualizações e possam não ser muito eficientes (esse conjunto de UNIONs produzindo "newdata" será lançado em uma tabela temporária, potencialmente no disco , e não haverá índice no ID nessa tabela temporária, o que pode ser significativo se houver muitas linhas).

    OBSERVAÇÃO: A conversão para BOUGHT_IN_CONTROL_PANEL_FILE_IDé garantir que o planejador de consulta conheça o tipo (já vi isso causar problemas no MSSQL com ele decidindo antecipadamente que a NULLdeveria ser e INT, em seguida, encontrando um tipo de caractere nessa posição em uma UNIONcláusula posterior - portanto, sempre especifico um tipo de coluna para NULLvalores literais ao usar UNION)

    Para tornar a primeira linha no mesmo formato das subsequentes, para tornar a geração programática desse tipo de instrução um pouco mais fácil, este hack fará o truque no MSSQL:

    UPDATE bicp 
SET    PARENT_ID                       = newdata.PARENT_ID                       
     , BOUGHT_IN_FORM_TYPE_ID          = newdata.BOUGHT_IN_FORM_TYPE_ID          
     , PRIORITY                        = newdata.PRIORITY                        
     , NAME                            = newdata.NAME                            
     , HEADING                         = newdata.HEADING                         
     , DESCRIPTION                     = newdata.DESCRIPTION                     
     , ICON                            = newdata.ICON                            
     , BOUGHT_IN_CONTROL_PANEL_FILE_ID = newdata.BOUGHT_IN_CONTROL_PANEL_FILE_ID 
FROM   (    SELECT ID                              = CAST(NULL AS INT)
                 , PARENT_ID                       = CAST(NULL AS INT)
                 , BOUGHT_IN_FORM_TYPE_ID          = CAST(NULL AS INT)
                 , PRIORITY                        = CAST(NULL AS INT)
                 , NAME                            = CAST(NULL AS NVARCHAR(MAX))
                 , HEADING                         = CAST(NULL AS NVARCHAR(MAX))
                 , DESCRIPTION                     = CAST(NULL AS NVARCHAR(MAX))
                 , ICON                            = CAST(NULL AS NVARCHAR(MAX))
                 , BOUGHT_IN_CONTROL_PANEL_FILE_ID = CAST(NULL AS INT)
           UNION ALL SELECT 109, 1, 28, 100, 'Tooling'  , 'Tooling'  , 'Enter your Machine Tools here', 'tooling.png' , null
           UNION ALL SELECT 1  , 0, 1 , 200, 'Bought In', 'Bought In', ''                             , 'boughtin.png', null
       ) AS newdata 
JOIN   bought_in_control_panel AS bicp ON newdata.ID = bicp.ID

    Advertência 1: esta deve ser uma sintaxe válida para MSSQL, mas eu não a testei e não sei quão padrão é toda a sintaxe, então você pode precisar de ajustes para postgres (por exemplo, nem todos os intérpretes de consulta permitem que você não nomeando as colunas após o primeiro SELECT em uma sequência de UNIONs).

    Advertência 2: isso parece um pouco hacky e é consideravelmente mais detalhado do que o ideal no seu exemplo.

    Como alternativa, solte os novos dados em uma tabela temporária e execute duas instruções para atualizar as linhas que existem nos dados atuais (então insira aquelas que não existem, se você estiver tentando em UPSERTvez de apenas UPDATE), envolvendo isso em um explícito transação para garantir que você obtenha consistência de tudo ou nada para a operação - isso provavelmente seria muito mais limpo, apesar de não ser uma única instrução.

    • 2

relate perguntas