[SOLVED] query boggs down my server and I cannot figure out why

[jakebur01]

This query boggs down my server and I cannot figure out why.

 

here is the query

mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b 
SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack
WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')") or die(mysql_error());

 

and this is what it prints

UPDATE smith118986 AS a, smith118986 AS b SET a.col8 =b.col8, a.col6=b.col6, a.col5=b.col5, a.col1=b.col1, a.col9=b.col9 WHERE LEFT(b.col2, CHAR_LENGTH(b.col2)-2)=a.col2 AND RIGHT(b.col2, 2) IN ('-1', '-2', '-3', '-0')

 

 

 

 

[jakebur01]

Would it be better to do something like this?

//suggested by premiso
$sql = "SELECT * FROM {$tablename} WHERE ($part LIKE '%-1' OR $part LIKE '%-2' OR $part LIKE '%-3' OR $part LIKE '%-0')";
$result = mysql_query($sql) OR DIE(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    $itemNum = substr($row[$part], 0, -2);
    $update = "UPDATE SET $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' WHERE itemNumber LIKE '{$itemNum}%'";
    mysql_query($update) or die(mysql_error());
}

 

 

[jakebur01]

It is throwing me this error.

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET col8 = ' $11.04 ', col6 = ' $5.52 ', col5 = ' $3.87 ', col1 = '419', col9 = ' at line 1

 

[Philip]

Would it be better to do something like this?

//suggested by premiso
$sql = "SELECT * FROM {$tablename} WHERE ($part LIKE '%-1' OR $part LIKE '%-2' OR $part LIKE '%-3' OR $part LIKE '%-0')";
$result = mysql_query($sql) OR DIE(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    $itemNum = substr($row[$part], 0, -2);
    $update = "UPDATE SET $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' WHERE itemNumber LIKE '{$itemNum}%'";
    mysql_query($update) or die(mysql_error());
}

 

It doesnt know which table to update.

[Psycho]

Since you have the table name as a variable I assume you are running this query on multiple tables. Are you running this query in a loop to update all the tables at once? Is there a particular reason you have multiple tables to capture all the same data?

 

Looking at your original query, it makes no sense to me if I am reading it right. You define the same table as 'a' and 'b' and then set column values in table a to the corresponding column values in table b. But, since they are the same table the end result would be no change!

 

The WHERE clause is equally perplexing since this condition would never be true

WHERE LEFT(b.col2, CHAR_LENGTH(b.col2)-2) = a.col2

a.col2 & b.col2 will be the same value, so the value of b.col2 minus two characters would never equal a.col2 (unless the values were empty to begin with).

 

The code you posted in your second post would be slow as well due to the looping queries you have. And it has additional problems, such as not identifying the table name in the 2nd query. If the 2nd query is being run on the same table as the first query, then you will end up with the same results as before - nothing. Because you are setting a field to its current value. If you are updating a different table, then it is still appears problematic, as it would only be copying the same values from one table into another. If these records are related then you should just enter a foreign key into the child record referencing the parent record.

[jakebur01]

I am updating records in the same table. I have part numbers ending with -1, -2, -3, -0 of which whose values need to be copied to the rows that do not contain that ending. Example: 35-184-3 's values would be copied to 35-184 if it exists.

[Psycho]

Hmm...

 

Well, obviously I am not the expert in what you are working with and trying to accomplish (you are), but it just seems to me that this is being over-complicated. And what you just stated still doesn't seem to coincide with the code you had posted where you are updating fileds with their current values.

a.$list =b.$list,

 

The variable list will have the same value when that is parsed and the field will set itself to it's current value. I would expect to see something more like this

a.$list1 =b.$list2,

 

If this is a one time process, then just break it down into smaller chunks. If not, then you might want to rethink the process.