Re: Multi row update SQL block
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...