Getting error "ORA-00933: SQL command not properly ended"

Question

I am able to get results on select statement but on using update i am getting issue. Database Server :- Oracle 11g.

update  t1
set t1.componentID = cteData.componentID
from tpinventryrecds t1
inner join ( select * from
(select TPLVLKEY , standardName , DOCTYPE , 'Default' as componentID ,count(*) as val from tpinventryrecds
where  componentID is  null
group by TPLVLKEY , standardName , DOCTYPE)`enter code here`
where val = 1) cteData
on t1.TPLVLKEY =  cteData.TPLVLKEY
and t1.standardName = cteData.standardName
and t1.DOCTYPE = cteData.DOCTYPE;

score 1 · Answer 1 · answered Apr 01 '19 at 08:30

You can't SET ... FROM - it, probably, has to be a subquery, such as this example; I'm not saying that it is correct, but - at least - it should compile.

UPDATE t1
   SET t1.componentID =
          (SELECT cteData.componentID
             FROM tpinventryrecds t1
                  INNER JOIN
                  (SELECT *
                     FROM (  SELECT TPLVLKEY,
                                    standardName,
                                    DOCTYPE,
                                    'Default' AS componentID,
                                    COUNT (*) AS val
                               FROM tpinventryrecds
                              WHERE componentID IS NULL
                           GROUP BY TPLVLKEY, standardName, DOCTYPE)
                    WHERE val = 1) cteData
                     ON     t1.TPLVLKEY = cteData.TPLVLKEY
                        AND t1.standardName = cteData.standardName
                        AND t1.DOCTYPE = cteData.DOCTYPE);

It is not working stating single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" — Shubham Jain, Apr 01 '19 at 08:35
Well, that's what you should fix by making sure that such a SELECT returns only one row, either by applying additional condition(s) in the WHERE clause, or - if possible - selecting DISTINCT componentID. — Littlefoot, Apr 01 '19 at 08:38

score 0 · Answer 2 · answered Apr 01 '19 at 08:34

0

use merge

MERGE INTO tpinventryrecds t
USING 
(
  select TPLVLKEY , standardName , DOCTYPE , 'Default' as componentID ,count(*) as val from tpinventryrecds
where  componentID is  null
group by TPLVLKEY , standardName , DOCTYPE
) cteData ON ( t1.TPLVLKEY =  cteData.TPLVLKEY
and t1.standardName = cteData.standardName
and t1.DOCTYPE = cteData.DOCTYPE)
WHEN MATCHED THEN UPDATE 
    SET t1.componentID = cteData.componentID

answered Apr 01 '19 at 08:34

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks It works ..! – Shubham Jain Apr 01 '19 at 10:21
Wants to know why it was not working same statement is working on SQL server but not on oracle – Shubham Jain Apr 01 '19 at 10:21
@ShubhamJain if it works mark as accepted and sql server and oracle is two different dbms so statement is varried – Zaynul Abadin Tuhin Apr 01 '19 at 10:29

Getting error "ORA-00933: SQL command not properly ended"

2 Answers2