stevens Posted July 6, 2006 Share Posted July 6, 2006 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. Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/ Share on other sites More sharing options...
craygo Posted July 6, 2006 Share Posted July 6, 2006 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 Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/#findComment-53906 Share on other sites More sharing options...
Wildbug Posted July 6, 2006 Share Posted July 6, 2006 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: 0mysql> 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?) Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/#findComment-53921 Share on other sites More sharing options...
stevens Posted July 6, 2006 Author Share Posted July 6, 2006 @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. Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/#findComment-53928 Share on other sites More sharing options...
.josh Posted July 6, 2006 Share Posted July 6, 2006 try echoing out $query and then enter it directly into your database (via phpmyadmin or whatever) Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/#findComment-53939 Share on other sites More sharing options...
stevens Posted July 6, 2006 Author Share Posted July 6, 2006 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? Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/#findComment-53949 Share on other sites More sharing options...
craygo Posted July 6, 2006 Share Posted July 6, 2006 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] Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/#findComment-54017 Share on other sites More sharing options...
stevens Posted July 6, 2006 Author Share Posted July 6, 2006 Thanks for all your help, i found out the problem was that $_post[id] wasnt being sent correctly. Thanks again. Link to comment https://forums.phpfreaks.com/topic/13852-updating-multiple-table-fields-with-data-from-another-table/#findComment-54029 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.