• Welcome, Guest. Please login.
 
September 17, 2019, 10:15:26 am

News:

Welcome to the SQLitening support forums!


adding modified last record to table

Started by bobl, April 28, 2011, 10:45:36 am

Previous topic - Next topic

bobl

April 28, 2011, 10:45:36 am Last Edit: April 28, 2011, 11:35:45 am by bobl
I created a small table

id,g,itm
======
1,NULL,y1
2,g,y2

and I want to add a modified version of the LAST record
i.e.
3,g,y3
where...the unique primary key changes...g remains the same and...y2 is changed to y3 i.e. 1 more than the '2' in y2.

I can do this using...
select * from tbl
do while slGetRow

loop
get flds 2 and 3
change fld3
INSERT INTO above tbl VALUES (NULL,fld2,fld3)...

but wondered if there's a COOOLER way?


Do_key_enter:
    #DEBUG PRINT "Do_key_enter:"
    LOCAL max_cols,num_pos AS LONG
    LOCAL flds(),rec,itm,non_num,num AS STRING
    slSel "Select * from cat_y1_s1"
    max_cols=slGetColumnCount
    DIM flds(2 TO max_cols)
    DO WHILE slGetRow
        rec=""
        FOR i= 2 TO max_cols
            IF i< max_cols THEN
                rec=rec & "'" & slF(i) & "',"
            ELSE
                itm=slF(i)
                num_pos = INSTR(itm, ANY "0123456780")
                non_num=MID$(itm,1,num_pos-1)
                ? non_num
                num=MID$(itm,num_pos)
                ? num
                rec=rec & "'" & non_num & STR$(VAL(num)+1) & "',"
            END IF
        NEXT
    LOOP
    rec=LEFT$(rec,-1)
    New_table_rec("cat_y1_s1", rec)  'supplies null for id to autoincrement
    InvalidateRect( hWnd, BYVAL %NULL, 1 )
    RETURN 

Fred Meier

Here is another way, there are probably many others.
      Local llA as Long
   Local lsA as String

   slOpen
   slExe "Begin"
   slExe "Create Table T1(C1,C2)"
   slExe slBuildInsertOrUpdate("T1", "A1" & $NUL & "B1")
   slExe slBuildInsertOrUpdate("T1", "C1" & $NUL & "D1")
   
   ' The following will insert 10 new records using data from the previous last record
   for llA = 1 to 10
      lsA = slSelStr("Select * from T1 Order by RowID Desc Limit 1")
      slExe slBuildInsertOrUpdate("T1", parse$(lsA, $BS, 1) & $NUL & left$(parse$(lsA, $BS, 2), 1) & format$(val(mid$(parse$(lsA, $BS, 2), 2)) + 1))
   next
   slExe "End"


bobl

Thanks very much Fred...That's much shorter!