Postgre를 사용하여 동일한 쿼리의 여러 행 업데이트SQL

Postgre를 사용하여 동일한 쿼리의 여러 행 업데이트SQL

Postgre에서 여러 행을 업데이트하려고 합니다.SQL을 하나의 문으로 만듭니다.다음과 같은 방법이 있습니까?

UPDATE table 
SET 
 column_a = 1 where column_b = '123',
 column_a = 2 where column_b = '345'

구문 및 매핑 테이블을 사용할 수도 있습니다.두 개 이상의 열을 업데이트하려는 경우 일반화가 훨씬 용이합니다.

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

원하는 만큼 열을 추가할 수 있습니다.

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

sql fiddle demo

@Roman 솔루션을 기반으로 여러 값을 설정할 수 있습니다.

update users as u set -- postgres FTW
  email = u2.email,
  first_name = u2.first_name,
  last_name = u2.last_name
from (values
  (1, 'hollis@weimann.biz', 'Hollis', 'Connell'),
  (2, 'robert@duncan.info', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

네, 다음 작업을 수행할 수 있습니다.

UPDATE foobar SET column_a = CASE
   WHEN column_b = '123' THEN 1
   WHEN column_b = '345' THEN 2
END
WHERE column_b IN ('123','345')

실증: http://sqlfiddle.com/ #!2/97c7ea/1

단일 쿼리에서 여러 행을 업데이트하려면 다음과 같이 하십시오.

UPDATE table_name
SET 
column_1 = CASE WHEN any_column = value and any_column = value THEN column_1_value end,
column_2 = CASE WHEN any_column = value and any_column = value THEN column_2_value end,
column_3 = CASE WHEN any_column = value and any_column = value THEN column_3_value end,
.
.
.
column_n = CASE WHEN any_column = value and any_column = value THEN column_n_value end

추가 조건이 필요 없는 경우 이 쿼리의 일부를 삭제하십시오.

ID 배열과 이에 상당하는 상태 배열이 있다고 가정해 보겠습니다.다음 예에서는 어레이의 정적 SQL(값이 달라도 변경되지 않는 SQL 쿼리)을 사용하여 이를 수행하는 방법을 보여 줍니다.

drop table if exists results_dummy;
create table results_dummy (id int, status text, created_at timestamp default now(), updated_at timestamp default now());
-- populate table with dummy rows
insert into results_dummy
(id, status)
select unnest(array[1,2,3,4,5]::int[]) as id, unnest(array['a','b','c','d','e']::text[]) as status;

select * from results_dummy;

-- THE update of multiple rows with/by different values
update results_dummy as rd
set    status=new.status, updated_at=now()
from (select unnest(array[1,2,5]::int[]) as id,unnest(array['a`','b`','e`']::text[]) as status) as new
where rd.id=new.id;

select * from results_dummy;

-- in code using **IDs** as first bind variable and **statuses** as the second bind variable:
update results_dummy as rd
set    status=new.status, updated_at=now()
from (select unnest(:1::int[]) as id,unnest(:2::text[]) as status) as new
where rd.id=new.id;

비슷한 시나리오를 접하게 되었고 CASE 표현이 나에게 유용했다.

UPDATE reports SET is_default = 
case 
 when report_id = 123 then true
 when report_id != 123 then false
end
WHERE account_id = 321;

Reports - 여기 테이블입니다.account_id는 위의 report_ids와 동일합니다.위의 쿼리는 1개의 레코드(조건과 일치하는 레코드)를 true로 설정하고 일치하지 않는 레코드 모두 false로 설정합니다.

@zero323이 제공하는 답변은 Postgre 12에서 매우 효과적입니다.에 대해 여러 개의 값을 가지고 있는 경우column_b(OP 질문에 기재)

UPDATE conupdate SET orientation_status = CASE
   when id in (66934, 39) then 66
   when id in (66938, 49) then 77
END
WHERE id IN (66934, 39, 66938, 49)

위의 질문에서id와 유사하다column_b;orientation_status와 유사하다column_a질문의 대상입니다.

데이터형 캐스트는 다른 답변, 주석 및 문서와 함께 사용법에 배치할 수 있습니다.이것에 의해, 카피 페이스트가 용이하게 됩니다.

update test as t set
    column_a = c.column_a::number
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where t.column_b = c.column_b::text;

@Roman 씨, 솔루션에 대해 감사합니다.노드를 사용하는 모든 사람에게 n개의 레코드로 n개의 컬럼을 갱신하기 위해 쿼리 문자열을 추출하는 유틸리티 메서드를 만들었습니다.

안타깝게도 같은 컬럼의 레코드는 n개밖에 처리되지 않기 때문에 recordRows 파라미터는 매우 엄격합니다.

const payload = {
  rows: [
    {
        id: 1,
        ext_id: 3
    },
    {
        id: 2,
        ext_id: 3
    },
    {
        id: 3,
        ext_id: 3
    } ,
        {
        id: 4,
        ext_id: 3
    } 
  ]
};

var result = updateMultiple('t', payload);

console.log(result);
/*
qstring returned is:

UPDATE t AS t SET id = c.id, ext_id = c.ext_id FROM (VALUES (1,3),(2,3),(3,3),(4,3)) AS c(id,ext_id) WHERE c.id = t.id
*/



function updateMultiple(table, recordRows){
  var valueSets = new Array();
  var cSet = new Set();
  var columns = new Array();
  for (const [key, value] of Object.entries(recordRows.rows)) {
    var groupArray = new Array();
    for ( const [key2, value2] of Object.entries(recordRows.rows[key])){    
      if(!cSet.has(key2)){
        cSet.add(`${key2}`);
        columns.push(key2);
      }
      groupArray.push(`${value2}`); 
    }
    valueSets.push(`(${groupArray.toString()})`);
  }
  var valueSetsString = valueSets.join();  
  var setMappings = new String();
  for(var i = 0; i < columns.length; i++){
    var fieldSet = columns[i];
    
      setMappings += `${fieldSet} = c.${fieldSet}`;
      if(i < columns.length -1){
        setMappings += ', ';
      }
  }
  var qstring = `UPDATE ${table} AS t SET ${setMappings} FROM (VALUES ${valueSetsString}) AS c(${columns}) WHERE c.id = t.id`;
  return qstring;
}

나는 받아들여진 답이 완전히 옳다고 생각하지 않는다.순서에 따라 다릅니다.다음 예시는 답변의 접근방식으로 올바르게 동작하지 않는 것입니다.

create table xxx (
    id varchar(64),
    is_enabled boolean
);

insert into xxx (id, is_enabled) values ('1',true);
insert into xxx (id, is_enabled) values ('2',true);
insert into xxx (id, is_enabled) values ('3',true);

UPDATE public.xxx AS pns
        SET is_enabled         = u.is_enabled
            FROM (
            VALUES
         (
            '3',
            false
         ,
            '1',
            true
         ,
            '2',
            false
         )
        ) AS u(id, is_enabled)
            WHERE u.id = pns.id;

select * from xxx;

그래서 문제는 여전히 남아 있습니다. 순서를 독립적으로 할 수 있는 방법이 있을까요?

----몇 가지 시도해 본 결과, 이것은 순서와 무관한 것 같습니다.

UPDATE public.xxx AS pns
        SET is_enabled         = u.is_enabled
            FROM (
            SELECT '3' as id, false as is_enabled UNION
            SELECT '1' as id, true as is_enabled UNION
            SELECT '2' as id, false as is_enabled
         ) as u
            WHERE u.id = pns.id;

언급URL : https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql