SQL Server 2022

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

create table users (id int, last_name varchar(100));
insert into users values (1,'Test1');
insert into users values (2,'Test2');
2 rows affected

create table upgrade (test_support varchar(100), 
  KEYED_NAME varchar(100), state varchar(100));
insert into upgrade values (1,'Testabc','Active');
1 rows affected

SELECT COALESCE(
    (SELECT LAST_NAME from users
            Where ID = (SELECT test_support FROM upgrade
            Where KEYED_NAME LIKE '%abc%'
            AND state = 'Active')), '-') AS LAST_NAME;


LAST_NAME


Test1


update upgrade set state = 'Inactive';
1 rows affected

SELECT COALESCE(
    (SELECT LAST_NAME from users
            Where ID = (SELECT test_support FROM upgrade
            Where KEYED_NAME LIKE '%abc%'
            AND state = 'Active')), '-') AS LAST_NAME;


LAST_NAME


-