Results 1 to 10 of 10

Thread: How to duplicate a column in the same table (SQLite)

  1. #1

    Thread Starter
    Hyperactive Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    329

    How to duplicate a column in the same table (SQLite)

    Hello!

    Using an SQLite db, I'm trying to duplicate all the records from one Column1 to the Column2, so Column1 has about 50 rows but only 2 records the rest of the cells have NULL values. Column2 is empty, it has nothing but NULL values. I've already googled it and I find nothing.

    My table has no PK, no constraints, no foreign Keys, and all columns are TEXT type.
    When I use the following code to try duplicating those records, it duplicates the same value in all the rows in Column2
    VB.NET Code:
    1. Using Kommand As New SQLiteCommand("UPDATE Employees SET Admin_06  = Admin_05, ConnectionSQL)
    2.                 Kommand.ExecuteNonQuery()
    3.             End Using

    This is what I get:

    Name:  Captura de pantalla 2020-09-30 000224.png
Views: 1123
Size:  14.9 KB
    I want to literally clone column Admin_05 into Admin_06.

    I'll appreciate any help.
    Last edited by Spybot; Sep 30th, 2020 at 12:11 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: How to duplicate a column in the same table (SQLite)

    You need to identify the row you want to update using a WHERE clause. That is what PKs are for and why pretty much all tables should have one. If you don't have a PK then you need to identify the row some other way. If you have to use all columns to do that then so be it. If you do that and there are multiple rows with the same set of values in every column then you'll still get multiple rows updated with that statement but it won't matter because the result will be the same.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: How to duplicate a column in the same table (SQLite)

    I'm more surprised his code compiles (if it compiles), since he's missing a closing paranthesis in his SQL-command
    As jmc said: you have to filter out the entries you want/don't want to update.

    Aircode:
    Code:
    UPDATE Employees SET Admin_06  = Admin_05 WHERE Admin_05 NOT ISNULL
    
    //or this way. Can't remember which way the NOT has to be
    UPDATE Employees SET Admin_06  = Admin_05 WHERE NOT Admin_05 ISNULL
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: How to duplicate a column in the same table (SQLite)

    Quote Originally Posted by Zvoni View Post
    I'm more surprised his code compiles (if it compiles), since he's missing a closing paranthesis in his SQL-command
    I assume that you mean the closing double-quote around the SQL code.
    Quote Originally Posted by Zvoni View Post
    As jmc said: you have to filter out the entries you want/don't want to update.

    Aircode:
    Code:
    UPDATE Employees SET Admin_06  = Admin_05 WHERE Admin_05 NOT ISNULL
    
    //or this way. Can't remember which way the NOT has to be
    UPDATE Employees SET Admin_06  = Admin_05 WHERE NOT Admin_05 ISNULL
    I'm not sure that that would work either, in the general case at least. I would expect that you'd still end up with one value in all the rows that get updated. That may be OK in this case if there is just one distinct value in Admin_05 but if there could be more than one then I think that you'd need something like:
    sql Code:
    1. UPDATE Employees
    2. SET Admin_06  = Admin_05
    3. WHERE Admin_05  = Admin_05
    4. AND Admin_06  = Admin_06
    5. AND ColumnX = ColumnX
    with a condition in the WHERE clause for every column. If there is some combination of columns that will be unique, even if it's not officially guaranteed to be, then you could just use those columns, otherwise all columns would be required. You might also include the NULL check that you mentioned to make it a bit more efficient, unless you actually wanted to copy NULL values from one column to the other. Not sure whether SQLite syntax is the same but, in SQL Server it would be Admin_05 IS NOT NULL.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to duplicate a column in the same table (SQLite)

    You need to identify the row you want to update using a WHERE clause.
    I'm feeling trepid about this but I don't think that's true. He wants to update all rows so doesn't need a where as a filter and the rows in a simple update statement (as opposed to an Update From) automatically refer to themselves so he shouldn't need it as a row identifier either. I am feeling trepid though so proved it for myself in SQLServer:-
    Code:
    create table #test (col1 text, col2 text)
    
    insert into #test(col1)
    Values(null)
    insert into #test(col1)
    Values(null)
    insert into #test(col1)
    Values('val')
    insert into #test(col1)
    Values('val')
    insert into #test(col1)
    Values(null)
    insert into #test(col1)
    Values(null)
    
    Select * From #test
    
    update #test set col2 = col1
    
    Select * From #test
    
    Drop Table #test
    ...that produces the correct result. I used SQLServer rather than SQLLite but the behaviour should be the same which makes me thing something else is going on here.

    Spybot, could you run a select against the table both before and after your update and post the results. NB, use a direct client (e.g. SQLiteBrowser or SQLiteViewer) rather than trying to do it via your own code. We need to make sure there's not something else going on in your code that's interfering with this.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: How to duplicate a column in the same table (SQLite)

    Quote Originally Posted by FunkyDexter View Post
    the rows in a simple update statement (as opposed to an Update From) automatically refer to themselves so he shouldn't need it as a row identifier either
    That's what I first thought but the result seemed to suggest otherwise, but if what I thought was happening was actually happening then I think that the result should have been all NULLs rather than all the same value, so I don't think I could be right regardless.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: How to duplicate a column in the same table (SQLite)

    Quote Originally Posted by jmcilhinney View Post
    I assume that you mean the closing double-quote around the SQL code.
    Errr...right...that one....

    Just after re-reading it, i got a suspicion regarding his SQL-command (especially regarding this missing double-quote):
    His result/screenshot suggests he's updating his admin_06-column with a String "admin_05" not the column-value.

    because i agree with FD: if the "old" value is a null, that null should transfer to the new column in its corresponding row, if the SQL-command is correctly formed
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to duplicate a column in the same table (SQLite)

    IMO there's no need for a special IsNull-Filter... and also no need for an ID-Field (in case of such a copy-op across the whole table)

    Just tested this with the VB6-COM-wrapper for SQLite - and it copies the Values over just fine (including the Null-Values),
    using a simple: "Update T Set Admin_06 = Admin_05"

    Here's my Test-Code (which dynamicall creates a Test-Table, insert 3 Demo-Records, and performs the Column-Copy):

    Code:
    Private Sub Form_Load()
      With New_c.MemDB
        .Cnn.MapDbNullToEmpty = False
        
        .Exec "Create Table T(Admin_05 Text, Admin_06 Text)" 'create a simple test-table "T"
        
        'Insert 3 Records (only affecting Column Admin_05)
        .Exec "Insert Into T(Admin_05) Values(Null)"
        .Exec "Insert Into T(Admin_05) Values('Admin 05')"
        .Exec "Insert Into T(Admin_05) Values(Null)"
        
        Debug.Print .GetCount("T") 'prints 3
        
        .Exec "Update T Set Admin_06 = Admin_05" 'copy values from Admin_05 to Admin_06
        
        With .GetTable("T") 'select the whole Table (all 3 records)
          Debug.Print IsNull(!Admin_05), IsNull(!Admin_06):  .MoveNext
          Debug.Print !Admin_05, !Admin_06:                  .MoveNext
          Debug.Print IsNull(!Admin_05), IsNull(!Admin_06)
        End With
      End With
    End Sub
    The above then prints out (for the last 3 Debug.Print Lines:
    Code:
    True          True
    Admin 05      Admin 05
    True          True
    IMO (if that simple statement doesn't work in your case),
    you've probably messed up the content of the new Admin_06-Column from a "former run".

    It would probably help, when you set the content of this Column explicitely to Null-Values in a prior statement like:
    "Update MyTable Set Admin_06 = Null"
    then followed by the real thing:
    "Update MyTable Set Admin_06 = Admin_05"

    HTH

    Olaf

  9. #9
    Member
    Join Date
    Jul 2019
    Location
    Ahmedabad
    Posts
    57

    Re: How to duplicate a column in the same table (SQLite)

    Hello, @Spybot
    Please try this query, To How to duplicate a column in the same table (SQLite)

    Code:
    SELECT DataId, COUNT(*) c FROM DataTab GROUP BY DataId HAVING c > 1;
    I hope this query useful to you.
    Thank you.
    < advertising removed by moderator >

  10. #10
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    Thumbs up Re: How to duplicate a column in the same table (SQLite)

    This helped and it supports BLOB/TEXT columns.

    Code:
    CREATE TEMPORARY TABLE temp_table
    AS
    SELECT * FROM source_table WHERE id=2;
    UPDATE temp_table SET id=NULL WHERE id=2;
    INSERT INTO source_table SELECT * FROM temp_table;
    DROP TEMPORARY TABLE temp_table;
    USE source_table;

    For more information follow this link:
    link

Posting Permissions

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



Click Here to Expand Forum to Full Width