I did change it to: UPDATE `table` SET `id` = `id` + 32768 WHERE `id` >= x
and then I decrement everything over 32768 by 32767 to get it back in line. This was required because a single update on a primary key did return an error about a key conflict when only incrementing by 1! Thanks, Gregor On 16-Jun-08, at 11:53 AM , Darren Duncan wrote: > Gregor, why did you do that more complicated version with the > subquery and > sorting et al? The short version that RBS would have worked a lot > better; > you just need to say? > > update binary_report_fmt > set column_id = column_id + 1 > where column_id > 1; > > ... and then insert a new row with column_id = 2. All that other > stuff you > did just makes things unnecessarily more complicated, and possibly > buggy. > > On a related matter, UPDATE statements are atomic operations, so the > fact > that the id is a primary key doesn't matter. Since you're > incrementing all > the id values simultaneously, there are no duplicate values at any > time, so > the primary key constraint would remain happy. > > -- Darren Duncan > > Gregor Brandt wrote: >> Hi, thanks this is great. Of course I forgot to mention that the id >> is a primary key, so I get an error about duplicate primary keys. >> >> I tried this: >> >> update binary_report_fmt set column_id = column_id + 1 where >> column_id >> = (select column_id from binary_report_fmt where column_id >= 3 order >> by column_id desc); >> >> but it only updates the last item. I guess I can make it a non- >> primary key..then it works perfectly. >> >> Gregor >> >> On 16-Jun-08, at 10:04 AM , [EMAIL PROTECTED] wrote: >> >>> update >>> table >>> set id = id + 1 >>> WHERE >>> id > 1 >>> >>> RBS > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----------------------------------------------------------------- Gregor Brandt Marionette Software Group Suite 202, 124 - 26 Ave SW Calgary, AB, Canada T2S 3G5 Tel: +1 403 401 4784 EMail: [EMAIL PROTECTED] www.marionette.ca _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
