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

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;

Littlefoot · Accepted Answer · 2019-04-01 08:30:30Z

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

Zaynul Abadin Tuhin · Accepted Answer · 2019-04-01 08:34:13Z

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

Wants to know why it was not working same statement is working on SQL server but not on oracle
@ShubhamJain if it works mark as accepted and sql server and oracle is two different dbms so statement is varried

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related