Reputation: 3442

php update multiple row

how do i update multiple row with one query?

table
id   ref_no   name
-------------------
1              a
2              b
3              c
4              e
5              f
...................

since i just created new ref_no column and its blank. and i expect the column id = ref_no

i tried below but giving me same ids:

$q = $db->query("SELECT id_user FROM user");
while($r = $q->fetch_array(MYSQLI_ASSOC)) :
    $db->query("UPDATE user SET user_no='".$r['id']."'");
endwhile;

Upvotes: 1

Answers (5)

ryryan

Reputation: 3928

Simply:

UPDATE user SET ref_no = id

Upvotes: 0

Nic

Reputation: 703

I dont think the other answers is what you want. If you need the id of each row to be equal to the ref:

$q = $db->query("SELECT id_user FROM user");
while($r = $q->fetch_array(MYSQLI_ASSOC)) :
    $db->query("UPDATE user SET ref_no='".$r['id']."' where id=".$r['id']);
endwhile;

Upvotes: 1

Álvaro González

Reputation: 146410

UPDATE user
SET ref_no = id
WHERE ref_no IS NULL

Be aware that, in SQL context, blank is a special concept that represented by the NULL keyword. You have to use IS NULL or IS NOT NULL to test against NULL.

Upvotes: 1