• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Updating a value in one row to a value in another row of same table in mysql

I have a customers table and want to update a value in (customer ID=x) col=a to (customer ID=y) col=c.
Current value in col a for customer ID=x is 1 and current value in col c for customer ID=y is 2

I want to run a query to update the 2 value in customer y (column name = Count) to be the same as the 1 value in customer x (column name = Subcount)
0
dynorich
Asked:
dynorich
  • 5
  • 5
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I had written an article about update/join:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

this should help to solve this: watch out that mySQL is a bit differently than the other dbms in regards to the syntax.
0
 
dynorichAuthor Commented:
Didn't see solution.

column names: cid   Count   Sponsorscount
              values:  61         1                 0
                            46          0                2
I need to update Sponsorscount from 2 to equal 1 (the value of Count in cid 61)

How would I write the code below to accomplish this?
UPDATE customers
SET Sponsorscount = Count
WHERE cid=46, cid=61
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's see if this works for you:
UPDATE customers 
  set Sponsorscount  = ( select o.`count` from customers o where o.cid = 61 )
WHERE cid = 46 

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
dynorichAuthor Commented:
Got the following error

#1093 - You can't specify target table 'customers' for update in FROM clause
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I remember that "issue" now, you cannot update, in mySql, from within the same table.
so, you have to run the query for the "select" part, store the results, and update using the intermedite results.
0
 
dynorichAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for dynorich's comment #a39923796

for the following reason:

Good response time and got the reason for my diffiulties and pointed me in another more doable direction.

Thank you
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Please close using my comments
0
 
dynorichAuthor Commented:
Solved the issue by creating a column in another table and then transfering  the value between the two tables.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I repeat that I commented that "in mysql, you cannot directly update a table from other rows from the same table" so one needs to first fetch the value to somewhere else, and then use it from there."
if that's a php variable, using a helper function, doing like the Dynorich posted, or whatever else, my comment is the "answer" to the original question: you cannot do it in a single step (like in all other dbms I know)
0
 
dynorichAuthor Commented:
I thought I had closed the question with thanks for the help but have had my question asking ability locked. I don't understand what I did wrong but thanks anyways
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now