Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-05-2007
02:46 PM

07-05-2007
02:46 PM

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!

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 1

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-06-2007
01:09 AM

07-06-2007
01:09 AM

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;

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;