Grab row info and set its value to another

Question

I want to grab a value of a row, remove the last 2 digits of it, and set it to another row. This is what I have so far.

while($row = mysqli_fetch_assoc($query)) {
     mysqli_query($con, "UPDATE my_table SET another_row = '" . substr($row['first_row'], 0, -2) . "'");
}

Table example

field1 | field2
100    |  0
200    |  0
5000   |  0

I want to set field2 to the value of `field`` minus the last 2 digits, so.

field1 | field2
100    |  1
200    |  2
5000   |  50

But it's setting the values of the rows to 0 for some reason.

Do you mean row or column? You are getting one row at a time with your query each with multiple columns — DrRoach, Aug 02 '17 at 15:16
For starters, you'll need a `WHERE` clause if you don't want to update your entire db — Funk Forty Niner, Aug 02 '17 at 15:19
Without any `WHERE` clause `UPDATE` operation will update all the rows of the table. Is this what you want? If so, then there's no need of `while` loop, simply fetch the first row from the result set and do the `UPDATE` operation. — Rajdeep Paul, Aug 02 '17 at 15:19
Sorry for the messy question. I edited it with a better explanation. — user6613235, Aug 02 '17 at 15:20
After your edit, it looks like you do mean column rather than row. — Don't Panic, Aug 02 '17 at 15:20
[Little Bobby](http://bobby-tables.com/) says **[your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)**. Learn about [Prepared Statements](http://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string)** is not safe! — GrumpyCrouton, Aug 02 '17 at 15:54

Ivo P · Accepted Answer · 2017-08-02T15:36:49.343

3

If they are integers, I suggest doing it mathematically and do the operation in 1 query

UPDATE yourtable
SET field2 = FLOOR(field1/100)
WHERE 1

so 500 will be FLOOR(5) which gives 5

555 will be FLOOR(5.55) which also gives 5

etc

edit: I first used round() but floor() seems better here

edit2:

to complete the example for the case field1 is a string:

UPDATE yourtable
SET field2 = SUBSTRING(field1, 1, CHAR_LENGTH(field1) - 2)
WHERE 1

edited Aug 02 '17 at 15:36

answered Aug 02 '17 at 15:23

Ivo P

But let's say they are not both integers and PHP had to be used, how would that be? – user6613235 Aug 02 '17 at 15:27
@user6613235 If they weren't integers, PHP still wouldn't need to be used. You could still do one update statement, but with MySQL string functions rather than math expressions. (Like this: https://stackoverflow.com/questions/6080662/strip-last-two-characters-of-a-column-in-mysql) – Don't Panic Aug 02 '17 at 15:28

1 Answers1