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.