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
