Why does my query produce error "ORA-00933: SQL command not properly ended"?

Question

My query:

CREATE VIEW cambiodatos AS 
SELECT 
    a.last_name||','||a.first_name AS "Nombre", 
    a.salary AS "Salario", 
    b.name AS "Nombre Departamento", 
    c.name AS "Nombre de Region"
FROM 
    s_emp a, s_dept b, s_region c
WHERE 
    a.dept_id = b.id AND b.region_id = c.id

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri'||','||first_name = 'Ben'

The error:

ORA-00933: SQL command not properly ended

Answer 1

Looks like you want an AND in the update

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri' AND first_name = 'Ben'

Answer 2

I haven't used Oracle at all during the last 7 years or so but don't you need a ; at the end of the statements?

Answer 3

First, I think that your UPDATE command is poorly formatted. Second, you are using fields from the underlying tables instead of the view that you are running the update against. Also, I don't think you can update a view that based on a join. See the response to this question. If you could might look like this.

UPDATE 
     cambiodatos 
SET 
     [Nombre de Region] = 'North America'
WHERE 
     Nombre = 'Biro, Ben'

Answer 4

CREATE VIEW cambiodatos AS SELECT a.last_name||','||a.first_name AS "Nombre", a.salary AS "Salario", b.name AS "Nombre Departamento", c.name AS "Nombre de Region" FROM s_emp a, s_dept b, s_region c WHERE a.dept_id = b.id AND b.region_id = c.id ; /* missing semicolon? */

UPDATE cambiodatos SET name = 'North America' WHERE last_name = 'Biri'||','||first_name = 'Ben' /* missing an And last_name = <> AND first_name = <> */

Answer 5

First, separate you queries with a semicolon and fix your SET conditions:

CREATE VIEW cambiodatos AS 
SELECT 
    a.last_name||','||a.first_name AS "Nombre", 
    a.salary AS "Salario", 
    b.name AS "Nombre Departamento", 
    c.name AS "Nombre de Region"
FROM 
    s_emp a, s_dept b, s_region c
WHERE 
    a.dept_id = b.id AND b.region_id = c.id;

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri'
     AND first_name = 'Ben'

That's the reason of your error ORA-00933

Second, your UPDATE statement will fail, as the view you created does not contain field name.

This query will compile:

UPDATE 
     cambiodatos 
SET 
     "Nombre de Region" = 'North America'
WHERE 
     "Nombre" = 'Biri, Ben'

, but most probably will fail as s_region is not key-preserved in this view.

To update, use this instead:

MERGE
INTO    s_region c
USING   (
        SELECT  b.region_id
        FROM    s_emp a, s_dept b
        WHERE   a.last_name || ',' || a.first_name = 'Biri, Ben'
                AND b.id = a.dept_id
        ) q
ON      c.id = q.region_id
WHEN MATCHED THEN
UPDATE
SET     c.name = 'North America'

Answer 6

The exact reason for why you're getting that error is that you have this WHERE-clause:

last_name = 'Biri'||','||first_name = 'Ben'

This is not legal syntax.

This would be:

last_name = 'Biri' AND first_name = 'Ben'

Or something like this:

name = 'Biri'||','||'Ben'

but then you could just write it like this:

name = 'Biri,Ben'

The problem is that it looks to me that you're using the second || there as an AND clause, but that doesn't fit in with the comma you're trying to add.

Perhaps you're trying to execute this?

last_name || ',' || first_name = 'Biri,Ben'

In any case, as others have pointed out, if you fix that syntax problem, you'll just get other error message about missing column names.