CODEWITHSUNDEEP
sqlpostgresql
user3206440
user3206440

Reputation: 5049

remove rows with more than one unique value for one column

I have two tables table1 and table2 - i want to merge these tables with any duplicate emp + carno rows removed. For example below - rows with emp as mike needs to be removed as mike has more than one unique carno in table1. Similarly rows with emp as sara need to be removed as sara has more than one unique carno across table1 and table2.

table1

+------+---------+
| emp  |   carno |
+------+---------+
| mike |  mh01   |
| sara |  mh02   |
| luke |  mh01   |
| mike |  mh04   |
+------+---------+

table2

+------+---------+
| emp  |   carno |
+------+---------+
| dave |  mh01   |
| sara |  mh06   |
| nite |  mh07   |
+------+---------+

output

+------+---------+
| emp  |   carno |
+------+---------+
| luke |  mh01   |
| dave |  mh01   |
| nite |  mh07   |
+------+---------+

Upvotes: 1

Views: 74

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use an INSERT INTO ... SELECT with appropriate logic. The basic stategy I used here is to union together the two tables, and then apply COUNT as an analytic function to figure out which employees appear in quantities greater than one. Then, restrict the union result to include only employee records where he or she appears once.

INSERT INTO output (emp, carno)
SELECT emp, carno
FROM
(
    SELECT emp, carno, COUNT(*) OVER (PARTITION BY emp) cnt
    FROM
    (
        SELECT emp, carno FROM table1
        UNION ALL
        SELECT emp, carno FROM table2
    ) t1
) t2
WHERE cnt = 1;

Here is what the output of the outer select gives:

enter image description here

Edit:

The answer by @zarruq is more efficient than this one, because it eliminates one subquery. But my approach can easily be adapted to filter out employees on any cardinality, with no change in performance.

Demo

Upvotes: 1

Rinshan techintl
Rinshan techintl

Reputation: 17

for deleting from multiple tables with different key

  delete a,b from table1 a innerjoin table2 b on b.carno != a.carno where a.emp ='mike' or b.emo = 'sara'

u can use variables instead of static by select query

Select a.emp as name1,b.emp as name2 from table1 a innerjoin table2 b on b.carno != a.carno

here u need to execute query and get name1 and name2 and save it into variable

delete a,b from table1 a innerjoin table2 b on b.carno != a.carno where a.emp ='$name1' or b.emo = '$name2'

for multiple entries in same table

first select

select count(*) as total from table1 where emp ='mike'; //or emp = '$yourvariable'

here u need to execute query and get total as count
if(total!=1)
{
delete a from table1 a where name ="mike"
}

Upvotes: 0

zarruq
zarruq

Reputation: 2465

Use union all in inner query to combine data from both tables andaggregation along with having count(emp) = 1 to filter out the employee with more than one record in outer query as below..

select t.emp, min(t.carno) as carno from 
(
select * from table1
union all
select * from table2
) t
group by t.emp
having count(t.emp) = 1
order by carno;

Result:

emp     carno
--------------
dave    mh01
luke    mh01
nite    mh07

DEMO

Update:

You can use INSERT INTO tablex(emp, carno) SELECT ..... to insert the output to a table

insert into tablex (emp, carno)
select t.emp, min(t.carno) as carno from 
(
select * from table1
union all
select * from table2
) t
group by t.emp
having count(t.emp) = 1
order by carno;

INSERT-SELECT DEMO

Upvotes: 2

Related Questions