| Deleteing records [message #400998] |
Fri, 01 May 2009 00:05  |
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 #401000 is a reply to message #400999] |
Fri, 01 May 2009 00:17  |
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 #401022 is a reply to message #400999] |
Fri, 01 May 2009 02:38 |
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 |
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 |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
