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