>说明:我试图通过将代码与部件号进行比较并更新行来将项目的定价从一个表转移到另一个表。临时表包含:
code price
M1 1.23
M2 4.56
M3 7.89
第二个表包含:
partnumber price
M2 0.00
M3 0.00
M1 0.00
问题是我随意地在 while 循环中嵌套了一个mysql_query,所以我得到了内存泄漏。我有大约 70,000 行数据,它在大约第 160 行爆炸了。T_T 我应该如何将临时表中的数据temp_yahooprices带到combined_stock?请记住,服务器只运行 PHP4,所以我无法编写准备好的 PDO 语句。相关代码粘贴在下面(并且 SQL 查询被注释掉):
//Import Price Data
echo 'Attempting to write Yahoo pricing data...
<div style="margin:20px; border:1px solid #C0C0C0; overflow:scroll; height:200px; width:50%;">';
$query = "CREATE TABLE temp_yahooprices
AS (SELECT DISTINCT
Code AS code,
`Sale-price` AS price
FROM nteproject.ynmdataz
ORDER BY Code);";
$result = mysql_query($query) or die('ERROR: Could not create temporary Yahoo pricing table. ' . mysql_error() . '<br>');
$query = "SELECT * FROM temp_yahooprices";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
$price = $row['price'];
$code = $row['code'];
echo '<b>' . $code . '</b> = ' . $price . '<br>';
//mysql_query("UPDATE combined_stock SET price=$price WHERE partnumber='$code'") or die("There was a problem updating pricing columns: " . mysql_error());
}
echo '</div> Pricing column was successfully created.<br><br>';
你可以用一个SQL语句很好地完成这一切:
UPDATE combined_stock dest LEFT JOIN (
SELECT price, code FROM temp_yahooprices
) src ON dest.partnumber = src.code
SET dest.price = src.price
这将基于temp_yahooprices中的数据创建一个名为 src
的临时表。然后,它会遍历您的目标表 (combined_stock) 并更新目标partnumber
等于源code
的所有元素并更新其价格。