Using MySQL row data to update another?

Question

I am creating a little game as a private project. Users can die and go into hospital I am looking for an easy way to update their information in one query but it involves updating one of the row's cell with another cell in the row. I am doing it so far like this:

// UPDATE USERS WHO SHOULD NOT BE IN HOSPITAL
$q = $dbc -> prepare("SELECT id, maxLifeforce FROM accounts WHERE hospitalTime < NOW() AND inHospital = 1");
$q -> execute();
while ($account = $q -> fetch(PDO::FETCH_ASSOC)) {
    $q = $dbc -> prepare("UPDATE accounts SET inHospital = 0, lifeforce = ? WHERE id = ?");
    $q -> execute(array($account['id'], $account['maxLifeforce']));
}

This works as I want but is there not a way to update all rows that meets the first statements criteria while setting lifeforce to that row's maxlifeforce if that makes sense. I heard it is not really that good practice to use queries in while loops.

mellamokb · Accepted Answer · 2011-12-14 18:36:11Z

2

I think you want something like this:

UPDATE accounts
   SET inHospital = 0,
       lifeforce = maxLifeforce
 WHERE hospitalTime < NOW()
   AND inHospital = 1;

Since you're wanting to pull data out of the same table to update other columns in the same row, it's as simple as saying SET lifeforce = maxLifeforce.

answered Dec 14, 2011 at 18:36

mellamokb

56.9k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cgwebprojects Over a year ago

Yes but would this be row specific? Whereas accounts can have different maxLifeforce?

mellamokb Over a year ago

@cgwebprojects: Correct. The SET statements run in the context of each individual row, so for each row, the lifeforce of that row is set to the value of the maxLifeforce of that row. That's one of the main points of the UPDATE statement. You can always try it once on test data and see if you get expected results.

Collectives™ on Stack Overflow

Using MySQL row data to update another?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related