Jump to content


Photo

Updating multiple table fields with data from another table


  • Please log in to reply
7 replies to this topic

#1 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 06 July 2006 - 03:11 PM

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:

$query = "UPDATE table1, table2 SET table1.order_no = table2.order_no WHERE table2.order_no = '$_POST[order_no]' AND table1.id = '$_POST[id]'";


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:

$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]'";


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.
I wish i'd have thought up www.500wordsite.com !

#2 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 06 July 2006 - 03:18 PM

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

#3 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 06 July 2006 - 03:37 PM

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:
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)


(BTW, this should have probably been posted in the MySQL help forum below since it's really an SQL question.  It is MySQL, right?)
Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#4 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 06 July 2006 - 03:41 PM

@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.
I wish i'd have thought up www.500wordsite.com !

#5 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 06 July 2006 - 03:57 PM

try echoing out $query and then enter it directly into your database (via phpmyadmin or whatever)
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#6 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 06 July 2006 - 04:08 PM

I entered this in phpmyadmin and it worked perfectly:

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';


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?
I wish i'd have thought up www.500wordsite.com !

#7 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 06 July 2006 - 05:28 PM

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

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


#8 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 06 July 2006 - 05:48 PM

Thanks for all your help, i found out the problem was that $_post[id] wasnt being sent correctly. Thanks again.
I wish i'd have thought up www.500wordsite.com !




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users