Jump to content


Photo

MySQL update statement


  • Please log in to reply
2 replies to this topic

#1 shaggycap

shaggycap
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 October 2006 - 08:49 AM

I am struggling with a SQL statement and hoped someone would be able to help :)

I have 2 tables:

customer_depots
contacts

Each 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_depots
and 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.

SELECT cd.ADDRESS_ID FROM customer_depots cd, contacts con WHERE con.CUSTOMER_DEPOT_ID = cd.CUSTOMER_DEPOT_ID AND con.ADDRESS_ID = 0
Hope someone can help :-*

thanks.








#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 04 October 2006 - 11:26 AM

UPDATE
contacts AS con
INNER JOIN
customer_depots AS cd
ON
con.CUSTOMER_DEPOT_ID = cd.CUSTOMER_DEPOT_ID
SET
con.address_id = cd.address_id
WHERE
con.address_id = 0
http://dev.mysql.com.../en/update.html

#3 shaggycap

shaggycap
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 October 2006 - 11:50 AM

Thanks Shoz


YOU THE MAN




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users