Jump to content

Updating multiple table fields with data from another table


stevens

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.