SQL Server - Cursor and Dynamic SQL
If I understand your question correctly, I think you want OK in the MyTable if you cannot find any row on the joined tables that meets your FAILED conditions.  I'm using NOT EXISTS and also made sure I'm only updating MyTable for the specific table being processed.

Let me know if I have misunderstood your requirements.

drop table if exists [MyTable]
drop table if exists [BICHATP]
drop table if exists [AFUP]


CREATE TABLE [AFUP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
    ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
    ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
    [Tabs] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
    ('AFUP'), ('BICHATP');


DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
    SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @cmd = 'update [MyTable]
                set [Check_column] = case when NOT EXISTS
                    (SELECT * FROM ' + @Tabs + ' where
                    left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')
                    then ''OK''
                         else ''FAILED''
                    end
               from ' + @Tabs + ' WHERE [MyTable].Tabs = ''' + @Tabs + '''';

               print @cmd
    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable




| Tabs    | Check_column |
|---------|--------------|
| AFUP    | FAILED       |
| BICHATP | OK           |