I'm trying to delete the duplicated rows ; no way ; I keep getting errors (Oracle); In fact, when I was creating the table I excut

Question

create table salesmen
(
    salesman_id int,
    name varchar(30),
    city varchar(30), 
    commission numeric(5)
)

insert into salesmen (salesman_id, name, city, commission) 
values (5001, 'james hoog', 'new york', 0.15)

insert into salesmen (salesman_id, name, city, commission) 
values (5002, 'nail knite', 'paris', 0.13)

insert into salesmen (salesman_id, name, city, commission) 
values (5005, 'pit alex', 'london', 0.11)

insert into salesmen (salesman_id, name, city, commission) 
values (5006, 'mc lyon', 'paris', 0.14)

insert into salesmen (salesman_id, name, city, commission) 
values (5007, 'paul adam', 'rome', 0.13)

insert into salesmen (salesman_id, name, city, commission) 
values (5003, 'lauson hen', 'san jose', 0.12);

Delete aliasName 
from 
    (Select 
         *,
         ROW_NUMBER() over (Partition by salesman_id, name, city, commission order by salesman_id) as rowNumber
     From 
         salesmen) aliasName  
Where   
    rowNumber > 1

output: 5001 james hoog new york 0 5002 nail knite paris 0 5005 pit alex london 0 5006 mc lyon paris 0 5007 paul adam rome 0 5003 lauson hen san jose 0 5003 lauson hen san jose 0 5006 mc lyon paris 0 5001 james hoog new york 0 #in fact when I created the table I excuted more than once so I got duplicated rows.

Your `DELETE` query does not really make much sense. See https://www.oracletutorial.com/oracle-basics/oracle-delete/ — PM 77-1, Jul 13 '22 at 19:43
https://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle — PM 77-1, Jul 13 '22 at 19:46
@PM77-1 This query is perfectly valid in T-SQL, for example, but in Oracle updatable view doesn't allow analitic functions — astentx, Jul 13 '22 at 19:49
@astentx - OP's query is: `DELETE FROM `. Please provide a reference to T_SQL that shows such syntax as legal. — PM 77-1, Jul 13 '22 at 19:59
@PM77-1 [DELETE](https://learn.microsoft.com/ru-ru/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver16): `delete [...] { { table_alias | ... } | @ table_variable } [ ] [ FROM table_source [ ,...n ] ] ...`. And [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=b3f21ba21e152379479a3b0dfc671718) — astentx, Jul 13 '22 at 20:22

score 1 · Accepted Answer · answered Jul 13 '22 at 19:44

This is table's contents:

SQL> select * From salesmen order by 1;

SALESMAN_ID NAME                           CITY                           COMMISSION
----------- ------------------------------ ------------------------------ ----------
       5001 james hoog                     new york                                0
       5002 nail knite                     paris                                   0
       5003 lauson hen                     san jose                                0
       5005 pit alex                       london                                  0
       5006 mc lyon                        paris                                   0
       5007 paul adam                      rome                                    0

6 rows selected.

SQL>

You said you want to delete "duplicates". Which duplicates? I don't see any.

If you run that delete statement (fixed, though - when you reference all columns using asterisk, you missed to use table alias), you get the following error:

SQL> delete
  2  from
  3      (
  4          select
  5              s.*,            --> alias missing here
  6              row_number()
  7              over(partition by salesman_id, name, city, commission
  8                   order by
  9                       salesman_id
 10              ) as rownumber
 11          from
 12              salesmen s     --> alias added here
 13      ) aliasname
 14  where
 15      rownumber > 1;
    (
    *
ERROR at line 3:
ORA-01732: data manipulation operation not legal on this view


SQL>

Right; so you can't really do that.

But, what does the subquery return, anyway?

SQL> select
  2              s.*,
  3              row_number()
  4              over(partition by salesman_id, name, city, commission
  5                   order by
  6                       salesman_id
  7              ) as rownumber
  8          from
  9              salesmen s;

SALESMAN_ID NAME            CITY       COMMISSION  ROWNUMBER
----------- --------------- ---------- ---------- ----------
       5001 james hoog      new york            0          1
       5002 nail knite      paris               0          1
       5003 lauson hen      san jose            0          1
       5005 pit alex        london              0          1
       5006 mc lyon         paris               0          1
       5007 paul adam       rome                0          1

6 rows selected.

SQL>

Aha; all rownumber values are equal to 1 (due to partition by clause), so ... condition you used: where rownumber > 1 wouldn't do anything, anyway.

Therefore, what are you really trying to do? Which rows do you find "duplicates" in that sample table?

in fact when I was creating the table I excuted some rows more than once so I got some duplicated rows in my table. — Franck yao, Jul 15 '22 at 18:43

I'm trying to delete the duplicated rows ; no way ; I keep getting errors (Oracle); In fact, when I was creating the table I excut

1 Answers1