CODEWITHSUNDEEP
mysqlsqlopencart
Michael Emerson
Michael Emerson

Reputation: 1813

Creating an mySQL UPDATE statement from values in a different table

Due to some unforeseen circumstances, an Open Cart table of customers that I have has had their address_id reset to 0 for all records, which now causes problems.

I do have a backup of the sql, however I only have it in INSERT form but since a few things have changed since that last backup, I'd prefer it to be an UPDATE statement.

So, rather than:

INSERT INTO oc49_customer (customer_id,firstname,surname,address_id etc.) VALUES ('1','Joe','Bloggs','1' ...), ('2','Jane','Doe','2'...)

I would prefer:

UPDATE oc49_customer SET address_id = 1 WHERE customer_id = 1

Is there a way to easily do this, since now I have two tables - the existing customer table and also a customer_temp table which is the backup with the correct address ids?

Upvotes: 0

Views: 37

Answers (2)

Ferran
Ferran

Reputation: 142

UPDATE oc49_customer
SET address_id = (SELECT address_id
  FROM oc49_customer_old
  WHERE (oc49_customer_id = oc49_customer_old_id)
WHERE EXISTS ( oc49_customer_id = oc49_customer_old_id )

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

yes, use a subquery

Update oc49_customer
  set address_id =
      (Select address_id from oc49_customer_old
       where customer_id = oc49_customer.customer_id)
From oc49_customer

Also try it without the From clause at all: 

Update oc49_customer
  set address_id =
      (Select address_id from oc49_customer_old
       where customer_id = oc49_customer.customer_id)

Upvotes: 2

Related Questions