CODEWITHSUNDEEP
phpmysqldatabase
hillcode
hillcode

Reputation: 150

Update value from one database to another database - fastest method

I have two tables. first datatable structure has nine columns but the important three are:

code | name | value

2D3 | name test | 0.12

the second table has the same three columns.

Now I want to update all rows of the first table with the values of table two where code AND name are the same as in table two.

So my current idea is to do a select of all rows of table 1 with the code and name columns, than check if a row with the same code and name exists in table 2, if yes get that value and do a UPDATE query in table 1 with value of table 2.

The problem is that the two tables are hugh and I am sure that I am not using the fastest method. anyone an idea for the fastest method to do this? Thank you very much!

EDIT: the query:

$getall = "SELECT code, name, value FROM table2";
$query =  mysqli_query($conn, $getall );

while($result = mysqli_fetch_array($query))
{
  $UpdateAll = "UPDATE table1 SET value = '".mysqli_real_escape_string($conn,$result["value"])."' WHERE name = '".mysqli_real_escape_string($conn,$result["name"])."' AND code = '".mysqli_real_escape_string($conn,$result["code"])."'";
  $result2 = mysqli_query($conn, $UpdateAll); }

Upvotes: 0

Views: 92

Answers (1)

Quagaar
Quagaar

Reputation: 1287

You speak of two databases but really mean two tables, don't you? In this case, you can do it with one update using a join, like this:

update table1 t1 
inner join table2 t2 on t2.code = t1.code and t2.name = t1.name 
set t1.value = t2.value;

Upvotes: 2

Related Questions