I have a table with three column ID, PARTYID, PARTYTYPE, I need to update all PARTYTYPE 'A' to 'B' for specific PARTYID. There will be 2 case

• if PARTYID have only one PARTYTYPE as 'A' then i need to update it to 'B'
• if PARTYID have both PARTYTYPE 'A' and 'B' then i need to delete 'A'

For example

Expected Result

CodePudding user response：

For this, you have to run 2 query:

UPDATE table1 SET PARTYTYPE = "B" WHERE PARTYTYPE = "A"

DELETE FROM table1 WHERE ID IN (SELECT ID FROM (SELECT * FROM table1) as tab GROUP BY PARTYID, PARTYTYPE HAVING COUNT(*) > 1)

CodePudding user response：

UPDATE and DELETE cannot be performed in one query. Use 2 separate queries.

Query 1. Update table, alter 'A' to 'B' when 'B' not exists and to NULL otherwise.

UPDATE test t1
LEFT JOIN ( SELECT t2. PARTYID, 1 B_EXISTS
            FROM test t2
            WHERE t2.PARTYTYPE = 'B' ) t3 USING (PARTYID)
SET t1.PARTYTYPE = CASE WHEN t3.B_EXISTS
                        THEN NULL
                        ELSE 'B' 
                        END
WHERE t1.PARTYID IN (1, 2, 3) -- specify needed PARTYID values
  AND t1.PARTYTYPE = 'A';

Query 2. Remove rows with NULLs.

DELETE 
FROM test
WHERE PARTYTYPE IS NULL;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1f9b310806821c166da7c384640ecf89

CodePudding user response：

You need to execute 2 queries :

• the first one to update your lines with only partyType as A
• the second one to delete lines with A and B data

Here my example with a tempTable :

declare @tempTable TABLE 
(
    Id int ,
    PARTYID int,
    PARTYTYPE varchar(1)
)


insert into @tempTable VALUES(1,1,'A')
insert into @tempTable VALUES(2,1,'B')
insert into @tempTable VALUES(3,1,'C')
insert into @tempTable VALUES(4,2,'A')
insert into @tempTable VALUES(5,2,'B')
insert into @tempTable VALUES(6,3,'A')


select * from @tempTable

update @tempTable
set PARTYTYPE ='B'
where Id in 
(
    select t.id from @tempTable t
    left outer join @tempTable tB on tB.PARTYID = t.PARTYID and tB.PARTYTYPE !='A'
    where
         t.PARTYTYPE ='A'
        and tB.Id is null
)

select * from @tempTable

delete from @tempTable
where Id 
in
    (
    select t.id from @tempTable t
    inner join @tempTable tA on tA.PARTYID = t.PARTYID and tA.PARTYTYPE ='A'
    inner join @tempTable tB on tB.PARTYID = t.PARTYID and tB.PARTYTYPE ='B'
    where 
        t.PARTYTYPE ='A'
    )

select * from @tempTable