Multi row update SQL block
Block meaning that I'm having a mental block... I have a db where customer group information is kept in a separate table from customer. Customer has a link to the group_id, and I have to come up with an SQL to periodically refresh the relationship. customer contains customer information, including customer -------- cust_id cust_name cust_phone cust_address group_id group_d contains entries of the form: group_d ------- cust_id group_id So periodically, I'd like to update the entire customer table's customer.group_id entries based upon entries in the group_d table. Every piece of documentation on SQL "update" doesn't seem to allow a single update statement to change values in multiple rows with different values based on the row being updated. Help from the experts, please? Also, I don't know anything about triggers, but I'd even be willing to learn how to run my update every time the group_d table was modified...
John White wrote:
> Block meaning that I'm having a mental block...
>
> I have a db where customer group information is kept in a separate
> table from customer. Customer has a link to the group_id, and I have
> to come up with an SQL to periodically refresh the relationship.
>
> customer contains customer information, including
>
> customer
> --------
> cust_id
> cust_name
> cust_phone
> cust_address
> group_id
>
> group_d contains entries of the form:
>
> group_d
> -------
> cust_id
> group_id
>
> So periodically, I'd like to update the entire customer table's
> customer.group_id entries based upon entries in the group_d table.
>
> Every piece of documentation on SQL "update" doesn't seem to allow a
> single update statement to change values in multiple rows with
> different values based on the row being updated. Help from the
> experts, please?
A very simple test yielded the results that I was expecting. Try:
update customer set group_id =
(select group_d.group_id from group_d
where group_d.cust_id = customer.cust_id)
where 1=1;
> Also, I don't know anything about triggers, but I'd even be willing to
> learn how to run my update every time the group_d table was
> modified...
See the IBM Informix Guide to SQL: Syntax for a start with triggers.
--
June Hunt