HELP MIGRATING DATA..pLeAsE

[jomama]

I am trying to populate a field of one table in my MySQL database with a field from another table. Is this possible with a SQL query. Here is the data structure

Table AllSold - approx 6,000 records
Table AllClients - approx 27,000 records

This is a query that I was trying :
Update AllSold set State.AllSold = (Select State.AllClients from AllClients) where PolicyID.AllClients = OldPolicy.AllSold;

I can't seem to figure a solution with a query so I am trying to build an application that will do the following:
1. Query the AllSold Table for all records
"select State, AllSoldID from AllSold;"
2. Start Looping recordset
3. With first record from recordset run a query:
"select State from AllClients where PolicyID = 'allsoldrecordset(OldPolicy)' "
4. Take the result from query and populate record from AllSold recordset with result from AllClients query
"Update AllSold set State = 'allclientsresult(State);"
5. Loop till all records from AllSold recordset have been updated

Can someone help me find a resolution to this problem.

[statrat]

from the manual:
"Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables"

so if you have mysql 4.0:

Update AllSold, AllClients set AllSold.State=AllClients.State
where AllClients.PolicyID = AllSold.OldPolicy;

??

btw, your query has the table.fieldname backwards i.e. "AllSold.State" not "State.AllSold".
and again, from the manual:
"Subqueries have been implemented in MySQL version 4.1. "

otherwise:

 $q=mysql_query("select AllClients.State as state, AllClients.PolicyID as pid from AllClients, AllSold where AllClients.PolicyID = AllSold.PolicyID");
while($row=mysql_fetch_array($q)){
		$update=mysql_query("update AllSold set State = '".$row['state']."' where PolicyID='".$row['pid']."'");
}