更新MySQL数据库中两个不同表的值

This is my table_start on MySQL database :

这是我在MySQL数据库上的table_start：

+--------+---------+-----------+
| userID | userTfc | userCount |
+--------+---------+-----------+
| 11655  | SN10    |        45 |
| 11655  | SN16    |        80 |
| 11655  | SN24    |       796 |
| 11655  | SN35    |        56 |
+--------+---------+-----------+

I need update the table_end on the same MySQL database in this mode :

我需要在这种模式下更新同一MySQL数据库上的table_end：

+--------+------------+------------------+------------+------------------+------------+------------------+------------+------------------+
| userID | userTfc_01 | userTfc_01_Count | userTfc_02 | userTfc_02_Count | userTfc_03 | userTfc_03_Count | userTfc_04 | userTfc_04_Count |
+--------+------------+------------------+------------+------------------+------------+------------------+------------+------------------+
| 11655  | SN10       | 45               | SN16       | 80               | SN24       | 796              | SN35       | 56               |
+--------+------------+------------------+------------+------------------+------------+------------------+------------+------------------+

For the same userID I need update :

对于相同的userID，我需要更新：

1. the first value of table_start column userTfc to column userTfc_01 of table_end;
2. table_start列的第一个值userTfc到table_end的userTfc_01列;
3. the first value of table_start column userCount to column userTfc_01_Count of table_end;
4. table_start列的第一个值userCount为table_end的userTfc_01_Count列;
5. the second value of table_start column userTfc to column userTfc_02 of table_end;
6. table_start列userTfc的第二个值为table_end的userTfc_02列;
7. the second value of table_start column userCount to column userTfc_02_Count of table_end;
8. table_start列的第二个值userCount为table_end的userTfc_02_Count列;
9. the third value of table_start column userTfc to column userTfc_03 of table_end;
10. table_start列userffc的第三个值为table_end的userTfc_03列;
11. the third value of table_start column userCount to column userTfc_03_Count of table_end;
12. table_start列的第三个值userCount为table_end的userTfc_03_Count列;
13. the fourth value of table_start column userTfc to column userTfc_04 of table_end;
14. table_start列的第四个值userTfc到table_end的userTfc_04列;
15. the fourth value of table_start column userCount to column userTfc_04_Count of table_end;
16. table_start列的第四个值userCount为table_end的userTfc_04_Count列;

Can you help me?

你可以帮我吗？

Thank you in advance for any help, really appreciated.

提前感谢您的任何帮助，非常感谢。

2 个解决方案

#1

First execute this query for your first table

首先对第一个表执行此查询

$query1 = "SELECT userTfc,userCount FROM  table_start WHERE userID='11655' ";

I am assuming after above query you got the result in an array lets say $result. Now you can update your second table using loop. For example :

我假设在上面的查询后你得到一个数组的结果让我们说$ result。现在，您可以使用循环更新第二个表。例如 ：

if(!empty($result)){ // check if array is not empty
for ($i=1; $i <= sizeof($result); $i++) { 
    $query2 = "UPDATE  table_end  SET userTfc_0'".$i."' = '".$result[$i]['userTfc']."' WHERE userID='11655' ";
    mysql_query($query2);
}
}

#2

You can try below query. Use order by , limit and offset of mysql. Order by your query then use limit to get one record and use offset to pick line number.

您可以尝试以下查询。使用mysql的order by，limit和offset。按查询排序然后使用limit来获取一条记录并使用偏移量来选择行号。

update table_end te 
set userTfc_01 = (select userTfc from table_start ts where ts.userid = te.userid order by userTfc limit 1 offset 0),
set userTfc_01_Count = (select userCount  from table_start ts where ts.userid = te.userid order by userTfc limit 1 offset 0),

set userTfc_02 = (select userTfc from table_start ts where ts.userid = te.userid limit 1 offset 1),
set userTfc_02_Count = (select userCount  from table_start ts where ts.userid = te.userid order by userTfc limit 1 offset 1),

set userTfc_03 = (select userTfc from table_start ts where ts.userid = te.userid order by userTfc limit 1 offset 2),
set userTfc_03_Count = (select userCount  from table_start ts where ts.userid = te.userid order by userTfc limit 1 offset 2),

set userTfc_04 = (select userTfc from table_start ts where ts.userid = te.userid order by userTfc limit 1 offset 3),
set userTfc_04_Count = (select userCount  from table_start ts where ts.userid = te.userid order by userTfc limit 1 offset 3) 
where the.userid = 11655