SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tab(
    id         INT, 
    id2        INT, 
    Solved1    VARCHAR(30), 
    Steps      VARCHAR(30), 
    Process    VARCHAR(30), 
    Solved2    VARCHAR(30)
);

INSERT INTO tab VALUES
(1,  1, 'yes', 'Three', 'ATAT', 'AF'),
(2,  2, 'yes',   'One', 'ATAT', 'FR'),
(2,  3, 'yes',   'One', 'ATAT', 'EG'),
(2,  4, 'yes',   'One', 'ATAT', 'AF'),
(3,  5, 'no' ,   'One', 'ABAT', 'AF'),
(4,  6, 'yes',   'One', 'ATAT', 'FR'),
(5,  7, 'no' ,   'One', 'AVAT', 'EG'),
(6,  8, 'yes',   'Two', 'SATT', 'FR'),
(6,  9, 'yes',   'Two', 'SATT', 'EG'),
(6, 10, 'yes',   'Two', 'SATT', 'AF');

SELECT * FROM tab;


id
id2
Solved1
Steps
Process
Solved2


1
1
yes
Three
ATAT
AF

2
2
yes
One
ATAT
FR

2
3
yes
One
ATAT
EG

2
4
yes
One
ATAT
AF

3
5
no
One
ABAT
AF

4
6
yes
One
ATAT
FR

5
7
no
One
AVAT
EG

6
8
yes
Two
SATT
FR

6
9
yes
Two
SATT
EG

6
10
yes
Two
SATT
AF


SELECT * 
FROM tab
WHERE id NOT IN (SELECT id FROM tab WHERE Solved1 = 'yes');


id
id2
Solved1
Steps
Process
Solved2


3
5
no
One
ABAT
AF

5
7
no
One
AVAT
EG


DELETE FROM tab
WHERE id NOT IN (SELECT id FROM tab WHERE Solved1 = 'no');
8 rows affected

SELECT * FROM tab


id
id2
Solved1
Steps
Process
Solved2


3
5
no
One
ABAT
AF

5
7
no
One
AVAT
EG

id	id2	Solved1	Steps	Process	Solved2
1	1	yes	Three	ATAT	AF
2	2	yes	One	ATAT	FR
2	3	yes	One	ATAT	EG
2	4	yes	One	ATAT	AF
3	5	no	One	ABAT	AF
4	6	yes	One	ATAT	FR
5	7	no	One	AVAT	EG
6	8	yes	Two	SATT	FR
6	9	yes	Two	SATT	EG
6	10	yes	Two	SATT	AF