Update Table1 with a value from Table2 1

[pastorandy]

Hiv

I have two tables and all I want to do is update the cust

with a value from the territory

The tables are related and have a foreign key.

Here's what I have thus far:

UPDATE cust c, territory t
SET c.t_office_id = t.t_office_id 
WHERE c.t_office_id = t.t_office_id
AND c.postcode1 = t.postcode1

I just keep getting the error:

You have an error in your SQL syntax near 'c, territory t SET c.t_office_id = t.t_office_id WHERE c.t_office_id = t.t_offic' at line 1

Any ideas?

 

[pastorandy]

I think the post_town needs to have single quotes around it because the field values are text and numbers. (e.g AB10)

 

[pastorandy]

Hi feherke

Many thanks for all of your help with this!

Just managed to get the table updated which will save me a lot of work.

Thanks

 

[pastorandy]

Hi

Just one more question!

how would I write a query to just update one column in the cust table with the number 1 and then increment the numbers for all of the customer rows.up to 5,000.

 

[feherke]

Hi

Assuming that the post_town field is unique and not null. Otherwise use the actual primary key instead.

<?php
$c=mysql_connect("localhost","myUser", "myPassword");
mysql_select_db("myDatabase");
$r=mysql_query("select post_town from territory order by post_town");
while ($a=mysql_fetch_array($r))
  print("update cust set [green][i]theColumn[/i][/green]=".(++$i)." where post_town=".mysql_real_escape_string($a[0]).";\n");
mysql_close($c);
?>

Feherke.

http://rootshell.be/~feherke/