CODEWITHSUNDEEP
sqloracle-database
Hotdin Gurning
Hotdin Gurning

Reputation: 1819

update multiple columns in oracle

I have query :

UPDATE SDM_KARYAWAN
SET (ID_DIVISI,ID_UNIT_KERJA,ID_JABATAN) = 
(
    SELECT ID_DIVISI,ID_UNIT_KERJA,ID_JABATAN FROM
        (
        SELECT TO_CHAR(TGL_SK,'YYYYMMDD') URUT,ID_DIVISI,ID_UNIT_KERJA,ID_JABATAN
        FROM SDM_KARYAWAN_JABATAN
        WHERE ID_KARYAWAN = '0081005'
        ORDER BY URUT DESC
        )DETAIL
    WHERE ROWNUM = 1
)X
WHERE ID_KARYAWAN = '0081005'

But show error like this : [Err] ORA-00933: SQL command not properly ended

Actually I can use this code :

UPDATE TABLE1 SET COL1 = 'A',COL2='B',COL3='C'...

but What i want is updating multiple columns like this :

UPDATE TABLE1 SET (COL1,COL2,COL3) (SELECT COL1,COL2,COL3 FROM TABLE2)

::EDIT:: I tried this :

SELECT A.ID_DIVISI,A.ID_UNIT_KERJA,A.ID_JABATAN,
        DETAIL.ID_DIVISI X,DETAIL.ID_UNIT_KERJA Y,DETAIL.ID_JABATAN Z
    FROM SDM_KARYAWAN A
    INNER JOIN 
    (
    SELECT ID_KARYAWAN, TO_CHAR(TGL_SK,'YYYYMMDD') URUT,ID_DIVISI,ID_UNIT_KERJA,ID_JABATAN
    FROM SDM_KARYAWAN_JABATAN
    WHERE ID_KARYAWAN = '0081005'
    ORDER BY URUT DESC
    )DETAIL
    ON DETAIL.ID_KARYAWAN = A.ID_KARYAWAN
    WHERE A.ID_KARYAWAN = DETAIL.ID_KARYAWAN
    AND ROWNUM = 1

The results :

ID_DIVISI    ID_UNIT_KERJA    ID_JABATAN    X    Y    Z
-----------------------------------------------------------
D1           D5000             D51000       D2  D200  D2100

Now, i want to update columns on table SDM_KARYAWAN with X,Y,Z value. what's missing from my query? pls.

Upvotes: 0

Views: 14207

Answers (3)

Aman Khan
Aman Khan

Reputation: 21

UPDATE <TABLENAME>
SET STATUS = CASE
             WHEN WID = 1 THEN 1
             WHEN WID = 2 THEN 0
             WHEN WID = 3 THEN 1
             END
WHERE WID IN (1,2,3);

NOTE: WHERE COMMAND IS NECESSARY...

Upvotes: 0

Anand
Anand

Reputation: 56

This will work. Please check.

UPDATE SDM_KARYAWAN
SET (ID_DIVISI,
    ID_UNIT_KERJA,
    ID_JABATAN) =
      (SELECT ID_DIVISI, ID_UNIT_KERJA, ID_JABATAN
         FROM (  SELECT TO_CHAR (TGL_SK,'YYYYMMDD')
                           URUT,
                        ID_DIVISI,
                        ID_UNIT_KERJA,
                        ID_JABATAN
                   FROM SDM_KARYAWAN_JABATAN
                  WHERE ID_KARYAWAN = '0081005'
               ORDER BY 1 DESC) DETAIL
        WHERE ROWNUM = 1)
WHERE ID_KARYAWAN = '0081005'

Upvotes: 4

Dgan
Dgan

Reputation: 10295

try this:

 UPDATE 
(SELECT 
Table1.Col1 as OLD1, Table2.Col1 as NEW1
Table1.Col2 as OLD2, Table2.Col2 as NEW2
 FROM Table1
 INNER JOIN Table2
-- ON Some Join Conditions
) t
SET t.OLD1 = t.NEW1,
SET t.OLD2 = t.NEW2

Upvotes: 0

Related Questions