Re: Query failing with strange error.

David Gilbert <dgilbert(at)velocet(dot)ca> writes:
> update customer_contact
> set next_billed=min(customer_services.eff_date)
> where customer_contact.conn_num=7698
> and customer_services.conn_num=7698
> and customer_services.inv_num=0

This is not a well-defined query --- exactly what do you think the
semantics should be? Over what set of rows is the MIN() taken, for
any particular target row to be updated? With only one WHERE clause,
you've got no way to control the set of rows the MIN() scans separately
from the set of rows the UPDATE targets.

SQL92 forbids such things outright:

<update statement: searched> ::=
UPDATE <table name>
SET <set clause list>
[ WHERE <search condition> ]

Syntax Rules

2) A <value expression> in a <set clause> shall not directly con-
tain a <set function specification>.

Postgres doesn't presently forbid it, but we probably should, because
the executor tends to get confused --- unsurprisingly considering that
there's no well-defined behavior for this case.

What I think you mean is

update customer_contact
set next_billed =
(SELECT min(customer_services.eff_date) FROM customer_services
where customer_services.conn_num=7698
and customer_services.inv_num=0)
where conn_num=7698

but that's just a guess about the intended behavior.

regards, tom lane