Updating multiple table fields with data from another table

[stevens]

Hi i am trying to update one tables fields with the data in another table, i have got this to work for one field but it does not seem to work for multiple fields. This is what i have got to work so far:

[code=php:0]$query = "UPDATE table1, table2 SET table1.order_no = table2.order_no WHERE table2.order_no = '$_POST[order_no]' AND table1.id = '$_POST[id]'";[/code]


Now the query above only updates the order_no, i need it to update 6 other fields. I tried this query for multiple updates but it did not update any of the fields in table1:

[code=php:0]$query = "UPDATE table1, table2 SET table1.order_no = table2.order_no, table1.order_name = table2.order_name, table1.order_id = table2.order_id WHERE table2.order_no = '$_POST[order_no]' AND table1.id = '$_POST[id]'";[/code]


Now the only way i can think of doing multiple updates is to do 6 different singular updates or to post all of the table2 fields into vars and then update into table1, but both of these seem to be a long way round. Is there not a query i could run that will update multiple fields in one table using the data from another table? thanks in advance for any reply.

[craygo]

Well In order to update multiple tables you would need a common field in both tables to link. Do you have a common field??

Ray

[Wildbug]

Updating as in your example works for me on a mockup, even without common fields.  Are you sure your data is in the tables as you expect it?  Are you sure the data from your POST form is correct?

Here's what I tried:
[code]
mysql> SELECT * FROM t1; SELECT * FROM t2;
+------+----------+------+
| id   | order_no | name |
+------+----------+------+
|    1 |     NULL | aaaa |
|    2 |     NULL | bbbb |
|    3 |     NULL | cccc |
+------+----------+------+
3 rows in set (0.00 sec)

+------+----------+------+
| id   | order_no | name |
+------+----------+------+
|    4 |       10 | axxx |
|    5 |       20 | bxxx |
|    6 |       33 | cxxx |
+------+----------+------+
3 rows in set (0.00 sec)

mysql> UPDATE t1,t2 SET t1.order_no=t2.order_no,t1.name=t2.name WHERE t2.order_no=20 AND t1.id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t1; SELECT * FROM t2;
+------+----------+------+
| id   | order_no | name |
+------+----------+------+
|    1 |       20 | bxxx |
|    2 |     NULL | bbbb |
|    3 |     NULL | cccc |
+------+----------+------+
3 rows in set (0.00 sec)

+------+----------+------+
| id   | order_no | name |
+------+----------+------+
|    4 |       10 | axxx |
|    5 |       20 | bxxx |
|    6 |       33 | cxxx |
+------+----------+------+
3 rows in set (0.00 sec)
[/code]


(BTW, this should have probably been posted in the MySQL help forum below since it's really an SQL question.  It [i]is[/i] MySQL, right?)

[stevens]

@craygo: The two tables are basically the same structure so some of the fields are same. There is one field that is a common field (word).

@wildbug: I cant understand it because if i do it to update an individual field it works fine, so i presume my $post info is correct, but if i do the query with multiple updates it does not update.

[.josh]

try echoing out $query and then enter it directly into your database (via phpmyadmin or whatever)

[stevens]

I entered this in phpmyadmin and it worked perfectly:

[code]UPDATE table1, table2 SET table1.order_no = table2.order_no, table1.cust_name = table2.cust_name, table1.cust_email = table2.cust_email, table1.cost = table2.cost WHERE temp.order_no = '1' AND details.word = 'someword';[/code]


Now im confused... so is it my $post that is wrong? but why would it work when only 1 field is to be updated in the query but not with multiple fields?

[craygo]

try changing this
[code]$query = "UPDATE table1, table2 SET table1.order_no = table2.order_no WHERE table2.order_no = '$_POST[order_no]' AND table1.id = '$_POST[id]'";[/code]

to this
[code]$query = "UPDATE table1, table2 SET table1.order_no = table2.order_no WHERE table2.order_no = ".$_POST[order_no]." AND table1.id = ".$_POST[id]."";[/code]

[stevens]

Thanks for all your help, i found out the problem was that $_post[id] wasnt being sent correctly. Thanks again.