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.

score 2 · Accepted Answer · answered Dec 14 '11 at 18:36

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 '11 at 18:36

mellamokb

56,094
12
110
136

Yes but would this be row specific? Whereas accounts can have different maxLifeforce? – cgwebprojects Dec 14 '11 at 18:37
1

@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. – mellamokb Dec 14 '11 at 18:40

Using MySQL row data to update another?

1 Answers1