Lakshya Goyal Lakshya Goyal - 1 year ago 88
MySQL Question

How to update a specific row in a table without a primary key in MySQL?

I am trying to use an UPDATE query in MySQL database table. I have primary key that auto increments in the table but I don't want to use this key when doing this query. This is because this column doesn't restart its count when records are deleted so there are gaps in the table. For example, if I delete row 25 and then INSERT a new row, the ID of that row/will be 26. So back to my question, I want to know how I can edit the values of one specific record/row. I have tried the following:

UPDATE `table_name` SET `first_Name` = "Robert" WHERE (SELECT * FROM `employees` LIMIT 1,1)

This was my try of updating the firs_Name in the second row only. But it doesn't work. There is some syntax error. Is there any other way to do this? Or can I solve the problem of the auto-incrementing column (I would prefer an answer that ignores this).

Thanks in advance!

Answer Source

ur User Table Looks like this

id    |   first_name
1     |   Robert
2     |   Sam

now u want to edit SAM so he is called Samuel.


Print the Users

Select * From Users

then build a Loop to give user back in a table. and then u can do ur Actions with it



        mysql_query("UPDATE Users SET first_name = '".$_POST['first_name']."' where id=".$_GET['userid']." LIMIT 1");
        $get_user_sql = "SELECT * FROM Users WHERE id = ".$_GET['userid']." LIMIT 1";
        $query_user = mysql_query($get_user_sql);
        $user = mysql_fetch_assoc($query_user);
        print '<form action="'.$_SERVER["PHP_SELF"].'" method="post">';
        print '<input type="text" name="first_name" id="first_name" value="'.$user['first_name'].'" />';
        print '<input type="submit" />';
    $sql = "SELECT * FROM Users";
    $query = mysql_query($sql);
    while($row = mysql_fetch_assoc($query))
        print '<a href="?userid='.$row['id'].'">edit user <b>'.$row['first_name'].'</b></a><br />';

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download