Re: Multi row update SQL block

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