Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015
    Posts
    6

    Unanswered: Simple Record Update

    Complete newbie (version 5.5.41-0ubuntu0.12.04.1). I have a simple table with one column (integer). I want to know how to increment the value of the record for a specific record (i.e. line) number.

    I know how to query the record:

    SELECT * FROM tbl LIMIT offset, 1

    but I want to modify the value to be the current value + 1. How would I do that? If it matters, I'm trying to do it from a C program.

    I've tried something like:

    update tbl set ColName = newval limit offset, 1

    but that doesn't work. Appreciate any tips/suggestions.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ther update syntax is
    UPDATE mytable
    SET mycolumn = blah
    WHERE <some form of filter>
    the UPDATED and SET are required the where is optional, if omitted ALL rows will be updated. think of the where clause as limiting the rows to be updated so no where clause means all rows.

    SQL databases are optimised for set operations. rahter than a traditional programming approach of iterating through items in a list the SQL engine does that iteration for you.

    So say you wanted to add 1 to a column in every row in your table
    Code:
    UPDATE my_table SET my_column = my_column + 1
    So say you want to add 1 to every column based on a value elsewhere (say if a column held todays date
    Code:
    UPDATE my_table SET my_column = my_column + 1
    WHERE my_date_column = '2015/01/30'
    BUT a where clause must be specific if you want to limit to a sepcific set of rows.
    so say you wanted to increment the current valeu of 50 then
    Code:
    UPDATE my_table SET my_column = my_column + 1
    WHERE my_column = 50
    ..that will set all rows whose my_column = 50 to 51

    but a SQL engine has now concept of line numbers UNLESS YOU specifically set one as part of your design

    Usually (not always but nearly always) a SQL table will have a primary key, something that uniquely identifies a row. that column that is the primary key MUST be unique (ie no other row has the same value. often thats what yoiu woudl use to identify a specific row to be updated (what you'd put in your WHERE clause). But SQL doesnt' require that on an update

    an update could affect a set of rows, a single row or nothing assuming there is/isnt a match for the where clause.
    if there is no where clause then it will affect all rows.

    if you need a concept of line number then either add one to the table, and use that as the primary key OR use soem other form of storage such as a sequential file / array / collection etc.
    if you do decide to add a column for line_No then there are several options
    ...the auto generated number value (providing you don't need contiguous numbers then its good enough for your purposes (its a sequential number starting from (usually 1) that represents the sequence rows were added. DO NOT rely on an autogenerated number to be contiguous or fill in the gaps.
    so if say your application required the ability to say delete row 54 and then decrement ALL rows whose index was more than 54 by one then you have to do that yourself YOU cannot use an auto generated number column for that. Similarly you cannot say insert a row AFTER 33 and adjust all subsequent numbers by +1.

    ..you have to do that yourself through your code.
    .f that is what you require then you'd need several app functions (in C) that did the work for you by issuing the appropriate SQL commands

    ..psueodcode
    function deleteline(value)
    check there is a row for that value
    IF there is
    issue a SQL query to delete the row containing the specified value
    issue a SQL query then subtracts 1 from every row whose value is more than the specified value
    else
    ... do nothing, cos the user is a comedianb who sent invalid data

    function addilne([OPTIONAL] value)
    if value is unspecified or the specified value is higher then the current highest valeu or illegal then add a new row with the current highest number
    issue a SQL query to find the current MAX value
    insert a row with that value + 1
    else
    in a transaction
    update all rows (add 1 to the line_no) with that value or above
    insert a new row

    you cna refien the design as required by your C design paradigm, add other helper fucntions (to say check if a row exists
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Healdem is right, you need to have something to uniquely identify the row you are updating. In MySQL there is the concept of AUTO_INCREMENT which automatically increments the value for the next record that is added. I have created a small explanation of how this works here http://www.it-iss.com/mysql/mysql-re...rement-values/
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jan 2015
    Posts
    6
    Thanks for the replies. To summarize, mySQL has no concept or knowledge of rows so I need to specifically add a column to contain the row number? I was really hoping to avoid that because I'll have more than 4.5E9 (i.e. billion) entries and having 2 columns instead of 1 will double the size of the DB.

    Here is what I don't understand:

    SELECT * FROM tbl LIMIT offset, count

    works as I fully expect on a single column table where offset is the row number - 1. How is this not being aware of row numbers? Seems to me "LIMIT offset, count" is a defacto WHERE statement. Clearly I'm not understanding something.
    Last edited by ken18; 01-30-15 at 19:15.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The order in which rows are returned in any database can never be guaranteed unless you add the ORDER BY clause. So a row that is returned as ROW 2 today might return tomorrow as ROW 5. This is dependent on rows being deleted and inserted. If this is still ok then using the LIMIT feature in MySQL will work.

    Also there are always trade offs in databases. Size versus performance in your case here. If you need to access ROW 4.5E9 then the database will scan all records keeping a counter until it reaches the row your are looking for. On the other hand to access a record with a unique identifier i.e. a stored row number access to the row is obtained in log2n accesses (or on 4.5E9 rows will find it in 32 accesses in the worst case).
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •