[SOLVED] copying data from one row to another

[jakebur01]

I am trying to find the part numbers that have -1, -2, -3, and -0 on the end and copy the data from this row over to the part number that does not have a dash ending. This data is stored in MySQL.

 

Example.

32-844-1          8.44        3.56        4.53        8        711

 

would copy to

 

32-844        -        -        3.56        -        711

[napurist]

I find it easier sometimes to export the data from mysql to excel, manipulate the data and import it back into mysql as required.

[premiso]

$sql = "SELECT * FROM tabl_name WHERE (itemNumber LIKE '%-1' OR itemNumber LIKE '%-2' OR itemNumber LIKE '%-3' OR itemNumber LIKE '%-0')";
$result = mysql_query($sql) OR DIE(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    $itemNum = substr($row['itemnumber'], 0, -2);
    $update = "UPDATE SET col1 = '{$row['col1']}', col2 = '{$row['col2']}' WHERE itemNumber LIKE '{$itemNum}%'";
    mysql_query($update) or die(mysql_error());
}

 

That is a rough example, you did not provide very good information, thus you get that.

[samshel]

u want to do it in one query?

 

u can fetch all records u want in PHP, fetch the values and update it with another query.

[jakebur01]

I am creating a little script that will take the price list our vendor gives us and turns it into a text file that will import into our inventory program.

 

Some of the part numbers have changed to a new number. (Ex. 34-942 to 34-942-3) And they will put 34-942 as no longer available with dashes in the price fields. Well, we may still have inventory for that item and we want the updated price. So I am trying to find all the part numbers that have -0, -1, -2, or -3 appended to the end of it and apply the values to the part numbers without the -0, -1 ,-2, -3. Note: There may not always be numbers without a dash. Ex. There may be a 23-043-3, but there may not be a 23-043.

 

I hope I am explaining better. I am trying.

[jakebur01]

The whole point of this is to get completely away from excel. A script we wrote for another one of these companies vendors saved them about 3 hours of time. In php all they had to do was upload the file, select which columns are which, then it makes all the calculations and ftp's it to their server.

 

Now I am working on a file for this vendor. They always have these weird part number changes.

[sasa]

UPDATE table_name AS a, table_name AS b 
SET a.field2 ='-', a.field3='-', a.field4=b.field3, a.field5='-'
WHERE LEFT(b.field1, CHAR_LENGTH(b.field1)-2)=a.field1 AND RIGHT(b.field1, 2) IN ('-1', '-2', '-3', '-0')

[jakebur01]

All of this data is in the same table.

[sasa]

ok

both table_name are same

[jakebur01]

But, isn't this setting all the values in the row as dashes. I am trying to take the values from the -0,-1,-2,-3 part numbers and update them into the part numbers without a dash based on the partnumber.

 

Like.....  84-232-2      5.55      3.33        2.35        700

 

84-232        -        5.55        -        -        700

 

So,  after the update 84-232 would then become the same as 84-232-2

 

84-232        5.55        3.33        2.35        700

[sasa]

OK

UPDATE table_name AS a, table_name AS b 
SET a.field2 =b.fiel2, a.field3=b.field3, a.field4=b.field4, a.field5=b.field5, a.field6=b.field6
WHERE LEFT(b.field1, CHAR_LENGTH(b.field1)-2)=a.field1 AND RIGHT(b.field1, 2) IN ('-1', '-2', '-3', '-0')

[jakebur01]

Something like....

 

SELECT FROM MYTABLE WHERE PARTNUMBER ENDS WITH -3, -2, -1, OR -0

 

WHILE LOOP{

 

GET PRICE FIELD VALUES

 

          UPDATE MYTABLE SET $MYROW[listPRICE = .................        WHERE PARTNUMBER = $MYROW[PARTNUMBER] WITHOUT -1, -2,-3, OR -0 ON THE END

 

 

      {

 

 

 

[jakebur01]

Does this look right?

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')");

 

By the way... Thank you for all of your help!

[sasa]

look right

but try and report