Help!

Database

Help!

Can anyone help me figure this one out?

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

Please help!
1 REPLY
Enthusiast

Re: Help!

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;