shaggycap Posted October 4, 2006 Share Posted October 4, 2006 I am struggling with a SQL statement and hoped someone would be able to help :)I have 2 tables:customer_depotscontactsEach table contain fields called 'customer_depot_id' and 'address_id'.What I want to do is update the address_id in the contacts table, with the value of the address_id in the customer_depots table, only where the customer_id values match in each table, AND the address_id in the contacts table is equal to zero.Does this make sense?So, update address_id in contacts,where address_id in contacts = address_id in customer_depotsand address id in contacts = zero.This is because a number of address_id's are missing from the contacts table, and I want to replace them with the correspondingvalue from the other table.I have got as far as a SELECT statement, which goes someway to explain things, but I need an UPDATE statement like I have explained above.[code]SELECT cd.ADDRESS_ID FROM customer_depots cd, contacts con WHERE con.CUSTOMER_DEPOT_ID = cd.CUSTOMER_DEPOT_ID AND con.ADDRESS_ID = 0[/code]Hope someone can help :-*thanks. Quote Link to comment Share on other sites More sharing options...
shoz Posted October 4, 2006 Share Posted October 4, 2006 [code]UPDATEcontacts AS conINNER JOINcustomer_depots AS cdONcon.CUSTOMER_DEPOT_ID = cd.CUSTOMER_DEPOT_IDSETcon.address_id = cd.address_idWHEREcon.address_id = 0[/code]http://dev.mysql.com/doc/refman/4.1/en/update.html Quote Link to comment Share on other sites More sharing options...
shaggycap Posted October 4, 2006 Author Share Posted October 4, 2006 Thanks Shoz[size=14pt][color=green][font=Verdana]YOU THE MAN[/font][/color][/size] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.