Jump to content

Archived

This topic is now archived and is closed to further replies.

stevens

Updating multiple table fields with data from another table

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?)

Share this post


Link to post
Share on other sites
@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.

Share this post


Link to post
Share on other sites
try echoing out $query and then enter it directly into your database (via phpmyadmin or whatever)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Thanks for all your help, i found out the problem was that $_post[id] wasnt being sent correctly. Thanks again.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.