-
Sep 30th, 2020, 12:07 AM
#1
Thread Starter
Hyperactive Member
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:
Using Kommand As New SQLiteCommand("UPDATE Employees SET Admin_06 = Admin_05, ConnectionSQL)
Kommand.ExecuteNonQuery()
End Using
This is what I get:
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.
-
Sep 30th, 2020, 12:31 AM
#2
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.
-
Sep 30th, 2020, 01:05 AM
#3
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
-
Sep 30th, 2020, 02:43 AM
#4
Re: How to duplicate a column in the same table (SQLite)
Originally Posted by Zvoni
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.
Originally Posted by Zvoni
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:
UPDATE Employees
SET Admin_06 = Admin_05
WHERE Admin_05 = Admin_05
AND Admin_06 = Admin_06
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.
-
Sep 30th, 2020, 02:58 AM
#5
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
-
Sep 30th, 2020, 03:21 AM
#6
Re: How to duplicate a column in the same table (SQLite)
Originally Posted by FunkyDexter
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.
-
Sep 30th, 2020, 04:26 AM
#7
Re: How to duplicate a column in the same table (SQLite)
Originally Posted by jmcilhinney
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
-
Nov 1st, 2020, 04:45 PM
#8
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
-
Nov 18th, 2020, 10:54 PM
#9
Member
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 >
-
Dec 16th, 2020, 11:15 PM
#10
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|