Home » SQL & PL/SQL » SQL & PL/SQL » Deleteing records (Oracle 10G)
Deleteing records [message #400998] Fri, 01 May 2009 00:05 Go to next message
TJPokala
Messages: 17
Registered: March 2008
Junior Member
Hi All,
How do we delete records with two paramenters?

For example, I would like to delete records of table1 which has a repetitive direction in a tower but only when they have the same SALE color.I must end up with only 3 records.

Sample data:
Comm Sale Tower Direction Station Station_Name Bearing Race
49416 RED S1 3 2878 Bay 315 100
49415 RED S1 2 2878 Bay 190 100
49414 RED S1 1 2878 Bay 70 100
49418 RED S1 2 2878 Bay 190 100
49419 RED S1 3 2878 Bay 315 100
49417 RED S1 1 2878 Bay 70 100

But to get the results from above,

I need to match two tables.

TABLE1
Comm
Sale

TABLE2
Comm
Tower
Direction
Station
Station Name
Bearing
Race

1.Both Table are connected by common identifier which is COMM.
2.It does not matter which COMM it deletes because there should only be one unique directions within a TOWER.

The example show the directions are duplicated within a tower.There should only be one direction and it doesn't matter which comm it deletes.

Instead of seeing 6 records, I woudl like to see just these records ignoring which ever COMM column Oracle deletes

49416 RED S1 3 2878 Bay 315 100
49415 RED S1 2 2878 Bay 190 100
49414 RED S1 1 2878 Bay 70 100


Please advice.Thank you!
Re: Deleteing records [message #400999 is a reply to message #400998] Fri, 01 May 2009 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member


You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/

Please, please, please Read & Follow Posting Guidelines above.

Go to the section labeled "Practice" & do as directed.





Post DDL for tables.

Post DML for test data.



Post expected/desired results.

Re: Deleteing records [message #401000 is a reply to message #400999] Fri, 01 May 2009 00:17 Go to previous messageGo to next message
TJPokala
Messages: 17
Registered: March 2008
Junior Member

DDLs

Create table1
( COMM number,
SALE varchar2(10));

Create table2
( Comm number,
Tower varchar2(5)
Direction varchar2(1)
Station number,
Station_Name varchar2(50),
Bearing number,
Race number
));

DMLs

to delete records

I tried this:

DELETE FROM table1
WHERE comm NOT IN (SELECT min(comm) FROM table2
where rowid IN
(SELECT rowid
FROM
(SELECT rowid,
row_number() over(PARTITION BY direction
ORDER BY direction) dup
FROM rae.table2
ORDER BY direction ASC)
WHERE dup > 1)
GROUP BY tower,direction
);

It deletesd some records,not sure what.But not that I want

My Expected Results:

Instead of seeing 6 records, I woudl like to see just these records ignoring which ever COMM column Oracle deletes

49416 RED S1 3 2878 Bay 315 100
49415 RED S1 2 2878 Bay 190 100
49414 RED S1 1 2878 Bay 70 100
Re: Deleteing records [message #401011 is a reply to message #401000] Fri, 01 May 2009 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Search before posting, for instance search for "delete duplicates".

Regards
Michel
Re: Deleteing records [message #401022 is a reply to message #400999] Fri, 01 May 2009 02:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@BlackSwan - is there any chance you could remove some of the blank lines in your posts - they're already totally devoid of any actual help - making them take up more space as well is just selfish.
Re: Deleteing records [message #401023 is a reply to message #400998] Fri, 01 May 2009 02:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would make this a two step process.
As Michel says, look for any of the standard Delete Duplicates solutions to delete records from Table 2, and then go back and delete records from Table 1 which no longer have values in Table 2.

There are alternatives - do you have a Foreign key relationship between Table 1 and Table 2?
Re: Deleteing records [message #401031 is a reply to message #401023] Fri, 01 May 2009 03:33 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hope following will help you.

SQL>select t1.id, t1.name, t2.area, t2.city
  2    from t1, t2
  3    where t1.id = t2.id;

        ID|NAME      |AREA                |CITY
==========|==========|====================|====================
         1|Naresh    |area1               |mumbai
         2|Naresh    |area2               |mumbai
         3|Naresh    |area3               |mumbai
         4|Naresh    |area1               |mumbai
         5|Naresh    |area2               |mumbai

5 rows selected.

SQL>select max(id) id_to_delete from t2
  2  group by area, city
  3  having count(id) > 1;

ID_TO_DELETE
============
           5
           4

2 rows selected.


Approach suggested by JRowbottom sir

regards,
Delna
Previous Topic: Delete duplicate rows
Next Topic: Clarification in 'Insert into' statement (merged)
Goto Forum:
  


Current Time: Sat Dec 10 14:48:57 CST 2016

Total time taken to generate the page: 0.08147 seconds