update multiple columns in oracle

Question

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.

what is the reason behind it

Pரதீப்
– Pரதீப்

2015-01-08 05:24:01 +00:00
Commented Jan 8, 2015 at 5:24 — Pரதீப்
– Pரதீப், Commented Jan 8, 2015 at 5:24

Himanshu · Accepted Answer · 2015-01-08 06:07:39Z

4

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'

edited Jan 8, 2015 at 6:07

Himanshu

4,41116 gold badges34 silver badges41 bronze badges

answered Jan 8, 2015 at 5:36

Anand

561 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Dgan · Accepted Answer · 2015-01-08 05:16:15Z

0

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

answered Jan 8, 2015 at 5:16

Dgan

10.3k1 gold badge33 silver badges51 bronze badges

2 Comments

Hotdin Gurning Over a year ago

i tried and show this error : [Err] ORA-01732: data manipulation operation not legal on this view

Dgan Over a year ago

@RubahMalam if Destination tables are view then you can't update it

Aman Khan · Accepted Answer · 2021-02-23 10:08:30Z

0

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...

answered Feb 23, 2021 at 10:08

Aman Khan

212 bronze badges

Collectives™ on Stack Overflow

update multiple columns in oracle

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related