syntax help with update statement

[Anon]

I need to do the following:

update table members
set amount='select cost from event'
where status='member'
and label='newevent';

I can't find any place that gives
me the syntax for this. can anyone help?
Thanks
B

[Anon]

set amount='select cost from event'

Shouldn't you be using a 'where' clause
on that line?

You say you used to do that... on what
kind of server?

Do all SQL engines allow sub-selects?

[Anon]

syntax varies among dbms.

if you are using mysql, then you will need to concatenate in order to build that statement as mysql does not have subselects.

SELECT CONCAT('UPDATE members set amount = ', cost, ' WHERE status = ', member, ' AND label = ', newevent, ';') FROM members m, event e WHERE ...

That's the basic idea, although the WHERE clause in the concat function could be simiplified to just use the key for the members table - if you join the tables correctly in the WHERE clause proper.

postgresql has better support for functions and subselects, btw.