Deleting by ROWID

Question

I'd like to run this query.

DELETE FROM A006873.GC_CLIENTS
WHERE ROWID = '14519';

But I get this error:

SQL.sql: Error (2,15): ORA-01410: invalid ROWID

I got in GC_CLIENTS

ID      NUMBER      NAME
14519   0000017690  VILLAVINE

I don't realize what's wrong if I'm in the correct ID. I'm using a sequence by the way and I don't want to delete it with a WHERE ID = '14519'.

What does `SELECT ROWID FROM GC_CLIENTS WHERE ID = '14519'` return? — Barmar, May 14 '15 at 15:33
What does using a sequence have to do with it? Assuming ID is a primary key, deleting by ID or ROWID will do exactly the same thing. — Mat, May 14 '15 at 15:35

score 0 · Answer 1 · answered May 14 '15 at 15:36

ID = 14519 not ROWID. ROWID is an internal identifier used to locate the value

SQL> select rowid from t.d;

ROWID
------------------
AAAVqIAAEAAAAInAAA
AAAVqIAAEAAAAInAAB
AAAVqIAAEAAAAInAAC

SQL> delete from t.d where rowid = 'AAAVqIAAEAAAAInAAA';

1 row deleted.

SQL> select rowid from t.d;

ROWID
------------------
AAAVqIAAEAAAAInAAB
AAAVqIAAEAAAAInAAC

What you want is just

DELETE FROM A006873.GC_CLIENTS
WHERE ID = 14519;

score 0 · Accepted Answer · answered May 14 '15 at 15:36

0

ROWID is a psuedocolumn that returns the address of the row.

You can try this if you dont want to delete by Id

delete from A006873.GC_CLIENTS
WHERE ROWID in (select rowid from A006873.GC_CLIENTS where id = '14519');

answered May 14 '15 at 15:36

Shankar

879
8
15

Thanks man, that helped a lot. I'd like to learn more about Oracle, do you know a good book? or something like that? – Pablo Calderón May 14 '15 at 15:47

Deleting by ROWID

2 Answers2