0

I have two tables.

  1. J_YAD_PICT (table name) , GALLERY_DISP_FLG (column)
  2. J_YAD_CGR_PICT (table name) , Y_CGR_FLG (column)

when Y_CGR_FLG not null in J_YAD_CGR_PICT, then GALLERY_DISP_FLG needed to set as 1.

I have executed this 

Update A
SET A.GALLERY_DISP_FLG = 
  (CASE WHEN b.Y_CGR_FLG IS NOT NULL) THEN '1' 
        ELSE A.GALLERY_DISP_FLG 
   END
FROM J_YAD_PICT A inner join J_YAD_CGR_PICT B 
on A.YP_ID = B.YP_ID;

But given this error :

SQL Error: ORA-00905: missing keyword

Could anyone help me ?

Sukma Arifin
  • 1

3 Answers3

0

which table you use alias A?

i suggest try like this

Update J_YAD_PICT A SET 
  A.GALLERY_DISP_FLG =   (CASE WHEN b.Y_CGR_FLG IS NOT NULL) THEN '1'  
                               ELSE A.GALLERY_DISP_FLG 
                         END 
FROM J_YAD_PICT A inner join J_YAD_CGR_PICT B  on A.YP_ID = B.YP_ID;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Adri Aulia Rakhman
  • 79
  • 2
  • 1
    `FROM` is not valid in an SQL UPDATE statement (in standard SQL and Oracle) –  Jul 11 '18 at 08:00
0

Something like this?

update j_yad_pict p set
  p.gallery_disp_flg = (select case when c.y_cgr_flg is not null then '1'
                                    else p.gallery_disp_flg
                               end
                        from j_yad_cgr_pict c
                        where c.yp_id = p.yp_id
                       )
where exists (select null from j_yad_cgr_pict c1
              where c1.yp_id = p.yp_id
             );

EXISTS part of the query is here so that you wouldn't update rows (to NULL) that don't have a match in J_YAD_CGR_PICT table.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

When update table J_YAD_PICT, must be this table connect from table in inner select

UPDATE J_YAD_PICT A
SET A.GALLERY_DISP_FLG = (
    SELECT CASE WHEN b.Y_CGR_FLG IS NOT NULL THEN '1' ELSE A.GALLERY_DISP_FLG END 
    FROM J_YAD_PICT A2 
        INNER JOIN J_YAD_CGR_PICT B ON A.YP_ID = B.YP_ID 
        WHERE A.ID = A2.ID);
phoniq
  • 228
  • 1
  • 5