Any way to subtract subtract the same value from all of the columns in a specific row?

Agemmon posted 5 months ago in General

Hello HeidiSQL community!

I am attempting to modify some database data for a video game and I am wondering if it is possible, with the latest version of HeidiSQL, to subtract the same value from all of the columns in a specific row.

To be more specific, the database container I wish to modify contains 8 rows (left to right) that each house 4,960 columns (top to bottom) of data. They are all simple integers. I do not wish to modify the data in row 1, row 2 and row 3. For rows 4 through 8, I want to subtract 3 from all of the values in all 4,960 columns for these 5 specific rows. Is there any way to do this, regardless of I can do multiple rows at once or each row separately? I do hope so, as it would be quite the undertaking to manually subtract 3 from each of the columns in those rows. That would be 24,800 manual adjustments.

Thank you for taking the time to read and reply!

ansgar posted 4 months ago

First, you should please fix your confusion about columns and rows to not confuse others as well:

  • from left to right, you have columns
  • from top to bottom, you have rows

So, you want to update column 4 to 8 in all rows. This is quite simple with an UPDATE query:

UPDATE mytable SET
  col4=col4-3,
  col5=col5-3,
  col6=col6-3,
  col7=col7-3,
  col8=col8-3;

Note that I don't know the table and column names, so you need to use the correct names for them.

Please login to leave a reply, or register at first.