CODEWITHSUNDEEP
mysqlsql
Ameer
Ameer

Reputation: 31

How to eliminate a specific row in mysql

id  manager_id   name

1      1         Tony
2      2         smith
3      2         harry
4      1         jack
5      1         william
6      2         steve
7      2         no name
8      2         john
9      2         no name

I used this query for get same last ordered id.

select t.* 
  from tablename t 
 where not exists (select 1 
                     from tablename 
                    where manager_id <> t.manager_id 
                      and id > t.id)

| id  | manager_id | name    |
| --- | ---------- | -----   |
| 6   | 2          | steve   |
| 7   | 2          | no name |
| 8   | 2          | john    |
| 9   | 2          | no name |

The above query is working fine. but now my need is Eliminating the specific rows where name = no name. So how to customize the query.

Upvotes: 1

Views: 57

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5141

Please use below query to eliminate no name,

select t.* 
from tablename t 
where not exists (select 1 
                 from tablename 
                where manager_id <> t.manager_id 
                  and id > t.id) and t.name != 'no name';

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a delete with inner join based on your query

delete t1 from  tablename t1
inner join  (
    select t.* 
    from tablename t 
    where not exists (
         select 1 
         from tablename where manager_id <> t.manager_id and id > t.id
    )

) t2 on t1.id = t2.id 
     and t2.name ='no name'

or based on your comment could be you just want set the value to null
in this case you need update

Update  tablename t1
inner join  (
    select t.* 
    from tablename t 
    where not exists (select 1 from tablename where manager_id <> t.manager_id and id > t.id)

) t2 on t1.id = t2.id
    and t2.name = 'no name'
set t2.name = NULL

Upvotes: 0

Related Questions