Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sammy.

Asked: February 22, 2017 - 2:03 pm UTC

Last updated: February 26, 2017 - 1:58 am UTC

Version: Oralce Sql Developer Version 4.2.0.16.260

Viewed 10K+ times! This question is

You Asked

Table 1: select * from FV

Table 2: select * From DB_FV_W

UPDATE DB_FV_W  Set FV_02 =
 (CASE          
 WHEN db.FV_02 IS NULL THEN             
     '0'          
 Else     
     fv.MD  
 END) 
FROM DB_FV_W d LEFT OUTER JOIN FV f On d.Store_ID = f.Store_ID 
WHERE d.year = '2016'


I am getting error

Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

--- Complete Details ---

CREATE TABLE DB_FV_W
   (   
 STORE_ID NUMBER(3,0) Not Null, 
 FV_02 NUMBER
    )
  
   select * from DB_FV_W
Insert into DB_FV_W (STORE_ID,FV_02) values ('111',null);
Insert into DB_FV_W (STORE_ID,FV_02) values ('121',null);
Insert into DB_FV_W (STORE_ID,FV_02) values ('131',null);
Insert into DB_FV_W (STORE_ID,FV_02) values ('141',null);
Insert into DB_FV_W (STORE_ID,FV_02) values ('151',null);

Create Table FV(Store_ID  NUMBER(3,0) not null,MD NUMBER(5,2),TD NUMBER(5,2))

Insert into FV(Store_ID,MD, TD) Values('111','-15.93','23.69');
Insert into FV(Store_ID,MD, TD) Values('151','-10.12','53.19');
Insert into FV(Store_ID,MD, TD) Values('121','-52.20','256.62');

select * from FV
-- when updated query is executed

--- "need to correct the update query" --
UPDATE DB_FV_W  Set FV_02 =
 (CASE          
 WHEN db.FV_02 IS NULL THEN             
     '0'          
 Else     
     fv.MD  
 END) 
FROM DB_FV_W d LEFT OUTER JOIN FV f On d.Store_ID = f.Store_ID 
WHERE d.year = '2016'

-- output required is Main table is DB_FV_W and value to be updated to column FV_02 is from table FV column MD
-- condition case is if the store id is not found in table FV then in main table FV_W column FV_02 must be set as zero
-- if storeid found the update FV_02 as per the value in FV column MD

and Connor said...

Thank you for the test case - it make's our life much easier


SQL> CREATE TABLE DB_FV_W
  2     (
  3   STORE_ID NUMBER(3,0) Not Null,
  4   FV_02 NUMBER
  5      );

Table created.

SQL>
SQL>
SQL> Insert into DB_FV_W (STORE_ID,FV_02) values ('111',null);

1 row created.

SQL> Insert into DB_FV_W (STORE_ID,FV_02) values ('121',null);

1 row created.

SQL> Insert into DB_FV_W (STORE_ID,FV_02) values ('131',null);

1 row created.

SQL> Insert into DB_FV_W (STORE_ID,FV_02) values ('141',null);

1 row created.

SQL> Insert into DB_FV_W (STORE_ID,FV_02) values ('151',null);

1 row created.

SQL>
SQL> Create Table FV(Store_ID  NUMBER(3,0) not null,MD NUMBER(5,2),TD NUMBER(5,2));

Table created.

SQL>
SQL> Insert into FV(Store_ID,MD, TD) Values('111','-15.93','23.69');

1 row created.

SQL> Insert into FV(Store_ID,MD, TD) Values('151','-10.12','53.19');

1 row created.

SQL> Insert into FV(Store_ID,MD, TD) Values('121','-52.20','256.62');

1 row created.

SQL>
SQL> select * from DB_FV_W;

  STORE_ID      FV_02
---------- ----------
       111
       121
       131
       141
       151

5 rows selected.

SQL> select * from FV;

  STORE_ID         MD         TD
---------- ---------- ----------
       111     -15.93      23.69
       151     -10.12      53.19
       121      -52.2     256.62

3 rows selected.

SQL>
SQL>
SQL> update DB_FV_W t
  2  set FV_02 =
  3    nvl(
  4      (select MD
  5       from   FV
  6       where  store_id = t.store_id
  7      ),0)
  8      ;

5 rows updated.

SQL>     select * from DB_FV_W;

  STORE_ID      FV_02
---------- ----------
       111     -15.93
       121      -52.2
       131          0
       141          0
       151     -10.12

SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Update using NVL

Sammy John, February 25, 2017 - 8:06 am UTC

Mr. Connor McDonald,

Thank you so much for the quick reply.

It very helpful for me.

Highly appreciated.......

One more question COALESCE() works similarly to NVL() ?

is there any performance issue for large number or data to be updated in a table.
Connor McDonald
February 26, 2017 - 1:58 am UTC

"One more question COALESCE() works similarly to NVL() ?"

Yes

"is there any performance issue for large number or data to be updated in a table. "

Maybe