--
-- ### Delete and update check with subquery condition
--

drop if exists table t1;
drop if exists table t2;

create table t1 ( primary a int not null, b string(20));
create table t2 ( a int, b string(20));

delete from t1 where a in ( select a from t2 where b = 'XXX');

insert into t1 values ( 1, 'xxx');
insert into t1 values ( 2, 'yyy');
insert into t1 values ( 3, 'yyy');
insert into t1 values ( 4, 'zzz');

insert into t2 values ( 1000, 'XXX');
insert into t2 values ( 2000, 'YYY');
insert into t2 values ( 3000, 'ZZZ');

select * from t1;

select a from t2 where b = 'XXX';

update t1 set a = 2000 where a = 1000;

update t1 set a = 2000 where a = 1;

-- in ( select a from t2 where b = 'XXX');

select * from t1;

delete from t1 where a in ( select a from t2 where b = 'XXX');

select * from t1;

select * from t2;

update t2 set a = ( select a from t2 where b = 'ZZZ' ) where a = 1000;

select * from t2;

