Why is this sql query giving syntax error?

Question

begin transaction;
update employee set id = id/10;
update sales set employee_id = employee_id/10;
commit;

The error message:

Error starting at line 1 in command:
begin transaction;
update employee set id = id/10;
update sales set employee_id = employee_id/10;
commit; 
Error report:
ORA-06550: line 4, column 8:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ( begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> << continue close current delete fetch lock
   insert open rollback savepoint set sql execute commit forall
   merge pipe purge
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I think you are missing an end; statement after the commit; all begin should end with an end. Try it, if it works, I will add as an answer — Jorge Campos
– Jorge Campos, Commented Dec 16, 2015 at 13:52
i´ve never heard of begin transaction in combination with an oracle database. I´d say that it interprets it as begin...end and is looking for the end statement. — SomeJavaGuy
– SomeJavaGuy, Commented Dec 16, 2015 at 13:53
I think there already an answer here: stackoverflow.com/a/11979726/460557 — Jorge Campos
– Jorge Campos, Commented Dec 16, 2015 at 13:54

Rahul Tripathi · Accepted Answer · 2015-12-16 13:56:30Z

4

Try this:

begin 
update employee set id = id/10;
update sales set employee_id = employee_id/10;
commit;
end;

answered Dec 16, 2015 at 13:56

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user366312 Over a year ago

Error starting at line 1 in command: begin update employee set id = id/10; update sales set employee_id = employee_id/10; commit; end; Error report: ORA-02290: check constraint (MSAQIB.EMPLOYEE_CHK1) violated ORA-06512: at line 2 02290. 00000 - "check constraint (%s.%s) violated" *Cause: The values being inserted do not satisfy the named check *Action: do not insert values that violate the constraint.

Rahul Tripathi Over a year ago

@anonymous:- Those errors clearly state that you are updating the values which are violating the constraints of your table columns. Please check the constraints and then make the query accordingly

AntDC Over a year ago

Mybe you shoukld drop the offending constraint, make your uopdates and then recreate the contstraint.

Jorge Campos Over a year ago

@anonymous in order to do what you want you will have to disable all constraints of employee table to another tables then you run this block.

Collectives™ on Stack Overflow

Why is this sql query giving syntax error?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related