Update query

[pmaonline]

Hi all, i need a query to change my customers_id number from an example

1000549 to 500549 and i made this query that doesn'work :


UPDATE customers SET customers_id = '50%' WHERE customers_id = '100%'


i need to change the first 3 digits from 100 to 50 and the other numbers stays the same.


Can someone help me with this ???

Thanks...

[Tangerine Dream]

i need to change the first 3 digits from 100 to 50 and the other numbers stays the same.
Hi, if so, then 1000549 --> 500549, not 500600. If customers_id is a string:



UPDATE customers
SET customers_id = CONCAT(CONVERT( ((0+LEFT(customers_id, 3))/2) USING latin1), CONVERT( RIGHT(customers_id,4) USING latin1) )
WHERE customers_id LIKE '100%';

[pmaonline]

yes tou're right, i tried your code in phpmyadmin and it didn't work

i have my customer table attatched

[Tangerine Dream]

Note how useful to post your CREATE TABLE statement :)


UPDATE customers
SET customers_id = customers_id - 500000
WHERE customers_id BETWEEN 1000000 AND 1009999;

[pmaonline]

thanks, its so simple and i was blind :thumbsup: