CODEWITHSUNDEEP
mysqldatabasesql-update
Antonio Mailtraq
Antonio Mailtraq

Reputation: 1407

Update values from two different tables on MySQL database

This is my table_start on MySQL database :

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

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

  1. the first value of table_start column userTfc to column userTfc_01 of table_end;
  2. the first value of table_start column userCount to column userTfc_01_Count of table_end;
  3. the second value of table_start column userTfc to column userTfc_02 of table_end;
  4. the second value of table_start column userCount to column userTfc_02_Count of table_end;
  5. the third value of table_start column userTfc to column userTfc_03 of table_end;
  6. the third value of table_start column userCount to column userTfc_03_Count of table_end;
  7. the fourth value of table_start column userTfc to column userTfc_04 of table_end;
  8. the fourth value of table_start column userCount to column userTfc_04_Count of table_end;

Can you help me?

Thank you in advance for any help, really appreciated.

Upvotes: 0

Views: 53

Answers (2)

Fahad Anjum
Fahad Anjum

Reputation: 1256

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. 

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

Upvotes: 0

Ravi Roshan
Ravi Roshan

Reputation: 570

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 : 

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);
}
}

Upvotes: 1

Related Questions