I need to write a code in sql that writes "del_row" in the column "Adjustment_name" when there are duplicated Id_numbers (e.g:234566) but just when one of the values in Phone_number start with A and other one start with B and in that case, it will write "del_row" just in the row in which the value in column "Phone_number" starts with "B". Imagine that I have two duplicated id_numbers and in one of them, the Phone_number starts with A and in the other row starts with "C". In this last situation, I don't want to write anything.

Many thanks!

CodePudding user response：

One approach

SELECT t.id_number, t.Phone_number, 
  CASE WHEN a.id_number IS NOT NULL THEN 'del_row' ELSE '' END as Adjustment_name
FROM mytable t
LEFT JOIN 
(SELECT id_number from mytable
 WHERE SUBSTRING(Phone_number FROM 1 FOR 1)='A') a
/* List of IDs that have a phone number starting with A */
ON a.id_number = t.id_number
AND SUBSTRING(t.Phone_number FROM 1 FOR 1)='B'
/* Only check for matching ID with A if this number starts with B */

CodePudding user response：

A rather crude approach would be as below (assuming your phones rank Axxx, Bxxx, Cxxx, Dxxx). If your phone numbering logic is different - which is not very clear from your req - you can adjust accordingly.

create table temp_table_1 as (
  select id_number, phone_number
   , case 
       when dense_rank() over(partition by id_number order by phone_number)>1 
       and phone_number like 'B%'
       then 'del_row'
     end adjustment_name
  from your_table_name
) with data;

drop table your_table_name;
rename table temp_table_1 to your_table_name;