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

On Thu, 12 Aug 2004 06:57:58 -0400, John White wrote:

You can't do this in pure SQL. You would have to write a stored procedure,
or host language (C4GL, esql/C, C-CLI, Perl-DBD/DBI, etc) program to make the
updates intelligently. The REAL problem is that this table structure
violates second normal form! The group_id and so the group_id/cust_id
mapping is replicated in two tables. You can just get rid of the group_d
table and replace it with a VIEW. I assume there is also a GROUP table that
describes the customer groups themselves, so the VIEW might look like:

CREATE VIEW group_d ON
SELECT g.group_id, c.cust_id
WHERE

> 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...

On Thu, 12 Aug 2004 15:52:42 -0400, Art S. Kagel wrote:

Sorry that got away from me. Ignore the original:

> On Thu, 12 Aug 2004 06:57:58 -0400, John White wrote:
>
You can't do this easily in pure SQL. You should rite a stored procedure,
or host language (C4GL, esql/C, C-CLI, Perl-DBD/DBI, etc) program to make
the updates intelligently. The REAL problem is that this table structure
violates second normal form! The group_id and so the group_id/cust_id
mapping is replicated in two tables. You can just get rid of the group_d
table and replace it with a VIEW. I assume there is also a GROUP table that
describes the customer groups themselves, so the VIEW might look like:

CREATE VIEW group_d ON
SELECT g.group_id, c.cust_id
FROM group g, customer c
WHERE g.group_id = c.group_id;

You COULD use a trigger on the table actually being modified to maintain the
other, in the meantime.

Art S. Kagel

>
>> 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...

johnjohn-gg@triceratops.com (John White) wrote in message news:<e3a21ac4.0408120257.6fc85b19@posting.google. com>...
> 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...

You need to get the SQL for Smarties book.

I agree with June on this. He got this right (I am not suprised). The
syntax is akward but it is done all of the time. It is basically a
correlated sub-squery where the assignment uses a select statement
that joins to the outside table.

update customer set group_id = ( select group_id from group_d where
customer.cust_id = group_d.cust_id ) where 1 = 1 ;

Which is exactly what June wrote and exactly the way it is done in
sql.

This will set customer.group_id to null where they don't exist in
group_d. If you want to preserve the old group_id's of customers that
no longer exist in the group_d table you just need to do this:

update customer set group_id = ( select group_id from group_d where
customer.cust_id = group_d.cust_id ) where cust_id in ( select cust_id
from group_d) ;

This will only update the customers in customer table that exist in
the group_d table.


Of course this is a normalization issue and you don't need group_id in
both tables but you didn't ask me that but I going to tell you anyway
for free.

If you need to know the group of a company you would just

select g.cust_id, g.group_id from customer c, group g where g.cust_id
= c.cust_id ;

You would then drop the group_id from customer table.

If you have too much code to change then use a view to reduce the
changes. It would include all of the customer table fields and then
the group_d group_id field. The view would basically be the join query
above.

Curtis Crowson wrote:
> I agree with June on this. He got this right[...]

He?

--
June Hunt