My mysql table name is: mytable Here is the table structure (one column and values) myid ----- X1 X3 X2 X6 X6 X9 X1 X7 X5 X4 X8 How can I write one line query to update (decrease 1) all myid values where myid >= X5 So the result will look loke the following: myid ----- X1 X3 X2 X5 X5 X8 X1 X6 X4 X4 X7
Here's the problem--your myid field is text--you can't do math on text like you expect. For example, 'X100' is actually less than 'X5', and 'X20' -1 is not going to equal X19. Why is this data text--how come there is a letter preceding the value? My suggestion is to break this data up into 2 fields--on that contains the preceding text and one that holds the numerical value after it. Then doing this will be simple.
Try following: UPDATE tbl_myid SET myid = CONCAT(LEFT(myid, 1), TRIM(LEADING 'X' FROM myid) - 1) WHERE TRIM(LEADING 'X' FROM myid) >= 5; Code (markup): Assumption: 1. If X is only 1 letter above will work perfect, if its fixed string with more letters, you may have to change TRIM parameter and LEFT parameter accordingly.