UPDATE table_2 SET call_dt = (select min(b.data_dt) from table_test b WHERE table_2._acct_num = b._acct_num and b.cntct_id > table_2.cntct_id and b.data_dt between table_2.data_dt and table_2.data_dt + 6);
I'm basically wanting to update each row of table_2 with the minimum date given these criteria. I get the error: Code = 3706. 3706: Syntax error: expected something between '(' and the 'select' keyword. Output directed to Answer set window
I don't think you can specify a SEL query in the SET claue.
You might want to try something like this.
UPDATE TABLE_2 FROM ( select b._acct_num, min(b.data_dt) from table_test b, table_2 t WHERE t._acct_num = b._acct_num and b.cntct_id > t.cntct_id and b.data_dt between t.data_dt and t.data_dt + 6 ) TT(_acct_num, data_dt) SET CALL_DT = TT.DATA_DT WHERE TABLE_2._ACCT_NUM = TT._ACCT_NUM;