0

At two different tables, The new value of [chargeINFO.u_chargewait] I want to add and save the existing [userinfo.u_charged] value. After the query operation, I want the value of [u_charged] to be saved as 150000.

enter image description here

I can't find it even if I search for the query statement. Please help me. Thank you.

$query = "UPDATE userinfo
INNER JOIN chargeINFO ON (userinfo.u_id = chargeINFO.u_id)
SET userinfo.u_charged = chargeINFO.u_chargewait";
Barmar
  • 741,623
  • 53
  • 500
  • 612
osori
  • 67
  • 4
  • Rather than updating a single field to reflect the "current state" based on other information in the database it is generally far more advisable to do a JOIN and SUM in the query, or create a view that does it for you. Otherwise you're going to run into the issue of missed/malformed/interrupted queries not updating that "current state" field and it will no longer represent reality. – Sammitch Dec 17 '21 at 01:09

1 Answers1

3

In order to get the result 150000 from the original u_charged = 50000 and u_chargewait = 100000, you need to add the two columns together, rather than just assigning one to the other. Then you can assign the result back to the column to update it.

UPDATE userinfo
INNER JOIN chargeINFO ON (userinfo.u_id = chargeINFO.u_id)
SET userinfo.u_charged = userinfo.u_charged + chargeINFO.u_chargewait
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Could you elaborate a little on the reasoning of yours? Perhaps by linking to existing Q&A material on site or writing an explanation? – hakre Dec 17 '21 at 02:42
  • 1
    @hakre I added some more details. – Barmar Dec 17 '21 at 02:44
  • I dunno if in the spirit of the question the intention of asking is arithmetic simplicity rather than perhaps a reference how this answers the question. I bet you had something in mind when writing the answer. Perhaps you thought it's just a typo? Who am I to judge? – hakre Dec 17 '21 at 02:46
  • I suspect the OP assumed that SQL his syntax would automatically combine the columns rather than replacing. Beginning programmers frequently tend to think the computer can guess their intentions. – Barmar Dec 17 '21 at 02:51
  • Well, that would be an interesting option for a database, auto ++updates. Wondering if ANSI SQL does not have something for it ... ;) – hakre Dec 17 '21 at 02:58
  • @hakre These are the same beginners who think you can write `if variable = 1 or 2 or 3` – Barmar Dec 17 '21 at 03:00
  • I didn't want to trivialize it, this is just an example of a simple `column + 1` in SQL: https://stackoverflow.com/q/9293900/367456 – hakre Dec 17 '21 at 03:00