MySQL SELECT within UPDATE of same table

[showman13]

I am attempting to update the value of one field within a table, by doing a select of the value of another field within the same table in a single UPDATE statement.

Is it possible? And if so, could you tell me what is wrong with the syntax of my query?

UPDATE position_table SET base_pos_id = (SELECT pos_id FROM position_table WHERE mem_id='$member' AND step='$new_step') WHERE mem_id='$member' AND step='$new_step'

This only happens one time for each new Step Upgrade for a member.

Other position records will in the future be split off of this one, and the base_pos_id will remain the same, but the pos_id will be different for each one.

If this is not possible, what would be the most efficient means of accomplishing this?

Would it be by doing a SELECT, capturing the data, and then doing an UPDATE?

Thanks in advance for your help.

Douglas

[showman13]

Well, this is what I decided to do until I get a response to the inquiry above...

$sql1="SELECT pos_id FROM position_table WHERE mem_id='$member' AND step='$new_step'";
                $result=mysql_query($sql1) or die(mysql_ereor);
                $row=mysql_fetch_array($result);
                $new_pos=$row[0];
            $sql2="UPDATE position_table SET base_pos_id = '$new_pos' WHERE pos_id='$new_pos'";
            $result=mysql_query($sql2) or die(mysql_ereor);

[dietdew12z]

UPDATE position_table SET base_pos_id = (SELECT pos_id FROM position_table WHERE mem_id='$member' AND step='$new_step') WHERE mem_id='$member' AND step='$new_step'

I don't see anything wrong with the syntax here. In this case it would probably be much easier to do:

update position_table set base_pos_id = pos_id where mem_id='$member' and step='$new_step'

But I used your query on my own database to set a value in one table using the value from a different table and it worked okay.

Now it *does* give an error if my select query returns more than one row.

[showman13]

Thank You dietdew12z,

That is exactly the answer I was looking for.

I thought there had to be an easier way to state it, but just wasn't seeing it...

Perfect..

Douglas