조건에 따라 SQL 쿼리의 UPDATE 문에서 값을 설정하기 위해 동적 열 이름을 설정하는 방법은 무엇입니까? (How to set dynamic column name to set value in UPDATE statement of SQL Query on the basis of condition?)

문제 설명

조건에 따라 SQL 쿼리의 UPDATE 문에서 값을 설정하기 위해 동적 열 이름을 설정하는 방법은 무엇입니까? (How to set dynamic column name to set value in UPDATE statement of SQL Query on the basis of condition?)

예상:</strong> sn=2인 행에서 date2 필드가 NULL이기 때문에 date1 필드의 날짜 값을 업데이트합니다.</p></li>

참고: 위의 UPDATE SQL 쿼리는 올바르지 않습니다.

참조 솔루션

방법 1:

I'm not sure where sn comes in, but hopefully the general method will help.

Update both fields in your update statement, but either set them to themselves or to the new date based on a case statement:

UPDATE DETAIL_BOX SET date2= case when date2 is null then date2 else to_date('2015‑10‑16', 'YYYY‑MM‑DD') end 
    , date1 = case when date 2 is null then to_date('2015‑10‑16', 'YYYY‑MM‑DD') else date1 end 

방법 2:

Please see if this helps .

Idea is to include all columns in one update statement and then on the basis of your criteria you can decide whether to let them change or let them remain as is for a particular row .

            UPDATE detail_box 
            SET NAME = CASE WHEN sn = 2 THEN 'gita' WHEN sn= 3 THEN 'Krish' ELSE NAME END  , 
            date1 = CASE WHEN date1 IS NULL AND sn = 2 THEN date1 WHEN date1 IS NOT NULL AND sn = 2 THEN to_date('24‑Nov‑2014') ELSE date1 END , 
            date2=CASE WHEN date2 IS NULL and sn= 3 THEN date2  WHEN date2 IS NULL and sn= 3 then to_date('24‑Nov‑2014') else date2  END 

방법 3:

I would put two answers (by BeanFrog and Prabhat Sharma) together. In my opinion it's good solution to update column to a new value or to itself in depend on criteria.

update detail_box
set
   name = decode(sn, 2, 'Gita', 3, 'Krish', name),
   date1 = (case when sn = 2 and date2 is null then to_date('2015‑10‑16', 'YYYY‑MM‑DD') else date1 end),
   date2 = (case when sn = 3 and date2 is null then to_date('2015‑10‑16', 'YYYY‑MM‑DD') else date2 end)

Please note, decode function is specific to ORACLE database. It could be changed to case structure if you want to have common code regardless to RDBMS vendor.

(by Bhuwan Prasad Upadhyay、BeanFrog、Prabhat Sharma、Nikolay Antipov)

참조 문서

1. How to set dynamic column name to set value in UPDATE statement of SQL Query on the basis of condition? (CC BY‑SA 2.5/3.0/4.0)