为什么我的查询产生错误“ORA-00933：SQL命令未正确结束”？

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

6 个解决方案

#1

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

你得到这个错误的确切原因是你有这个WHERE子句:

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.

问题是,在我看来你正在使用第二个||有一个AND子句,但不适合你想要添加的逗号。

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.

无论如何,正如其他人所指出的那样,如果你修复了这个语法问题,你只会得到关于缺少列名的其他错误消息。

#2

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

首先,用分号分隔查询并修复SET条件:

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

这就是你的错误原因ORA-00933

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

其次,您的UPDATE语句将失败,因为您创建的视图不包含字段名称。

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.

,但很可能会因为s_region在此视图中未保存密钥而失败。

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'

#3

Looks like you want an AND in the update

看起来你想在更新中使用AND

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

#4

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?

在过去的7年左右的时间里,我根本没有使用过Oracle,但是你不需要一个;在声明的最后?

#5

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命令格式不正确。其次,您使用的是基础表中的字段,而不是您正在运行更新的视图。此外,我认为您不能更新基于连接的视图。查看对此问题的回复。如果你可能看起来像这样。

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

#6

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? */

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' /* missing an And last_name = <> AND first_name = <> */

UPDATE cambiodatos SET name ='North America'WHER last_name ='Biri'||','|| first_name ='Ben'/ *缺少an和last_name = <> AND first_name = <> * /

#1