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?

 

[r937]

this doesn't make sense --

SET c.t_office_id = t.t_office_id
WHERE c.t_office_id = t.t_office_id


you're setting the office id to the other office id only if they're already equal

seems kinda pointless to me...



r937.com | rudy.ca

 

[feherke]

Hi

Syntax errors :
[ul]
[li]you can not alias the table beeing updated[/li]
[li]you can not specify multiple tables[/li]
[/ul]
Logic error :
[ul]
[li]updating the field with the same expression as the condition will not change too much[/li]
[/ul]
The solution is like this :

[b]update[/b] cust
[b]set[/b] postcode1=(
  [b]select[/b]
  postcode1
  [b]from[/b] territory
  [b]where[/b] t_office_id=cust.t_office_id
)

Feherke.

http://rootshell.be/~feherke/

 

[pastorandy]

Hi

I'll explain a little:

In my territory table I have this:

territory_id, t_office_id, post_town
     1             2           AB12
     2             6           AB13

In my cust table i have this:

cust_id, t_office_id, post_town
 15          null       AB12

At the moment there is over 5,000 customers in the cust table but I need to assign the t_office_id from the territory table and update it in the cust table that is currently set to null.

the field t_office_id which I need to

 

[feherke]

Hi

[b]update[/b] cust
[b]set[/b] t_office_id=(
  [b]select[/b]
  t_office_id
  [b]from[/b] territory
  [b]where[/b] post_town=cust.post_town
)
[b]where[/b] t_office_id [b]is[/b] null

Feherke.

http://rootshell.be/~feherke/

 

[pastorandy]

Hi Feherke

Just getting...

You have an error in your SQL syntax near 'select t_office_id from territory where post_town=cust.post_town ) wh' at line 3

 

[r937]

seoandy, that error message indicates that you are on a pre-4.1 version

r937.com | rudy.ca

 

[pastorandy]

Is that a pre-4.1 version of mysql?

How would I go about doing this in my version then? It would take me days to manually do 5,000 updates individually!

 

[r937]

starting with version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

so if you are on 4.0.4 or higher, you can try


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

although, as i pointed out earlier, you aren't really changing anything

if you are on a version prior to 4.0.4 you are outta luck




r937.com | rudy.ca

 

[pastorandy]

Hi
Apologies for my ignorance here!

Are you talking about mysql version or php?

 

[feherke]

Hi

A very ugly workaround :

[b]create table[/b] cust_temp [b]as select[/b] c.cust_id,t.t_office_id,t.post_town [b]from[/b] cust c [b]inner join[/b] territory t [b]using[/b] (post_town);

[b]truncate[/b] cust;

[b]insert into[/b] cust [b]select[/b] * [b]from[/b] cust_temp;

[b]drop table[/b] cust_temp;

Feherke.

http://rootshell.be/~feherke/

 

[pastorandy]

Can that be run from a PHP query?

 

[feherke]

Hi

It would take me days to manually do 5,000 updates individually!

You can generate them. This works from a Unix/Linux shell :

mysql -D [green][i]database[/i][/green] -u [green][i]username[/i][/green] -B -s -e "select t_office_id,post_town from territory" | while read o p; do echo "update cust set t_office_id='$o' where post_town='$p';"; done | mysql -D [green][i]database[/i][/green] -u [green][i]username[/i][/green]

Feherke.

http://rootshell.be/~feherke/

 

[pastorandy]

Hi

I am currently doing the dev on my laptop before copying the database to the server.

My platform is:
Windows XP
Sokkit Contol Panel - Version 4:0
PHP Version 5.0.3
Apache/2.0.53 (Win32)
MySQL 3.23.52-max-debug

 

[feherke]

Hi

<?php
$c=mysql_connect("[green][i]hostname[/i][/green]","[green][i]username[/i][/green]");
mysql_select_db("[green][i]database[/i][/green]");
$r=mysql_query("select t_office_id,post_town from territory");
while ($a=mysql_fetch_array($r))
  mysql_query("update cust set t_office_id=".mysql_real_escape_string($a[0])." where post_town=".mysql_real_escape_string($a[1]));
mysql_close($c);
?>

Feherke.

http://rootshell.be/~feherke/

 

[pastorandy]

Hi

I did this

<? include ("./includes/session.php");

$r=mysql_query("select t_office_id,post_town from territory");

while ($a=mysql_fetch_array($r))

$updateTerritory = mysql_query("update cust set t_office_id=".mysql_real_escape_string($a[0])." where post_town=".mysql_real_escape_string($a[1]));

$Result = mysql_query($updateTerritory) or die (mysql_error());
?>

But it just comes back with query is empty!

 

[feherke]

Hi

In my code was only one instruction. You changed it with two instructions but forgot to enclose them in braces ( {} ).

Feherke.

http://rootshell.be/~feherke/

 

[pastorandy]

Hi
just tried this

$c=mysql_connect("localhost","myUser", "myPassword");
mysql_select_db("myDatabase");
$r=mysql_query("select t_office_id,post_town from territory");
while ($a=mysql_fetch_array($r))
mysql_query("update cust set t_office_id=".mysql_real_escape_string($a[0])." where post_town=".mysql_real_escape_string($a[1]));
mysql_close($c);

It doesn't produce any errors but doesn't update the cust table either.

How would I echo this query to see if it is doing the correct thing?

 

[feherke]

Hi

I have this in the seoandy.php file :

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

I run it with this command :

php seoandy.php

And it produces this result :

update cust set t_office_id=11 where post_town=1;
update cust set t_office_id=22 where post_town=2;
update cust set t_office_id=33 where post_town=3;
update cust set t_office_id=44 where post_town=4;
update cust set t_office_id=55 where post_town=5;

Which I can execute directly :

php seoandy.php | mysql -D test -u bugs

Or can be sent to a script file and executed from there :

php seoandy.php > seoandy.sql

mysql -D test -u bugs < seoandy.sql

In your previous posts quoted my code first with short open tag, than without PHP code tags. Are you sure the version you executed was syntactically correct ?

Feherke.

http://rootshell.be/~feherke/

 

[pastorandy]

Hi Feherke

Many thanks for your help!

This is strange because when I run your code

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

I can see the correct update statements but when I change the print for mysql_query it doesn't update the database.

I can't see why this doesn't update!