sql select then update results where

Question

I am trying to update Qty column where suppliers_stock_code = '" . $itemno . " but products_model does not contain '--'

I have tried

$sql = "UPDATE products set suppliers_qty = '" . $qty . "' (SELECT suppliers_stock_code, products_model from products where products_model NOT LIKE '%--%' and products_status = '1' and suppliers_stock_code = '" . $itemno . "')";

But this does not appear to insert the values can anyone see what is wrong with this line?

I have also tried

$sql = "update products set suppliers_qty= '" . $qty . "' where " . PRODUCTS_MODEL . " = '" . $selected['products_model'] . "' and " . SUPPLIERS_MODEL . "= '" . $itemno . "' and " . PRODUCTS_MODEL . " NOT LIKE '%--%'";

This works but takes to long to execute so was hoping to pre select the items to update.

Heres the full script maybe there is a better (faster) way of doing it?

<?php
$working_dir = '../feeds/csv';  
$local_file = 'test.csv';  
$type_sep = ",";  
$item_pos = 2;  
$qty_pos = 3;  
$item_pos -= 1;  
$qty_pos -= 1;  
chdir($working_dir);   
$handle = fopen($local_file, 'w');  
 require('includes/configure.php');  
 require('includes/functions/database.php');  
 tep_db_connect_script() or die('Unable to connect to database server!');  
$lines = file($local_file);  
foreach ($lines as $line) {  
  $items = explode  ($type_sep, $line);  
  $itemno = $items[$item_pos];  
  $qty = $items[$qty_pos] * 0.10;  

 $sql = "UPDATE products set suppliers_qty = '" . $qty . "' 
    WHERE products_model NOT LIKE '%--%' 
    AND products_status = '1' AND suppliers_stock_code = '" . $itemno . "'";
    $result = mysql_query($sql);  
 }  
 }   
 tep_db_close_script();  
 echo 'Finished and closed database connection';  
?>

score 0 · Accepted Answer · answered May 25 '14 at 11:46

0

You should use your second SQL:

$sql = "update products set suppliers_qty= '" . $qty . "' where " . PRODUCTS_MODEL . " = '" . $selected['products_model'] . "' and " . SUPPLIERS_MODEL . "= '" . $itemno . "' and " . PRODUCTS_MODEL . " NOT LIKE '%--%'";

The first one won't work, because the update will be executed on preselected results and not on a "real" table (btw: in your 1. sql you have to select the suppliers_qty column too if you want to update it (even without physical effort)).

When the sql takes to long it probably because you have many data (or an inefficient DBMS, or another bottleneck :) )

answered May 25 '14 at 11:46

chresse

5,486
3
30
47

If I try UPDATE products set suppliers_qty = 1000 WHERE products_model NOT LIKE '%--%' AND products_status = 1 AND products_model like '%CT%' directly in myphpadmin the query executes in 0.1720 sec – ShaGGy May 25 '14 at 15:39
just added it to Opening post – ShaGGy May 25 '14 at 16:06
1

ah ok. How many lines do you have in your `text.csv`? When phpmyadmin executes one sql statement in 0.1720 sec, then it's probably cause of the size of the `text.csv` file – chresse May 25 '14 at 16:17
there 3600 line with 4 columns (all small data in each field) if it is reading this straight from the csv could I load this into an array or something to increase the speed. – ShaGGy May 25 '14 at 16:33
3600 times an Operation which takes each 0.172 sec you have a runtime more than 10 minutes (maybe a little faster cause of caching... But this is the reason) – chresse May 26 '14 at 10:38
yeah never thought of it that way :) So i guess i`ll have to keep it how i had it as there seems no way to improve this speed. – ShaGGy May 27 '14 at 22:10
Yes looks like your right damn no way to speed it up :) – ShaGGy Jun 05 '14 at 23:56

sql select then update results where

1 Answers1