What commands to combine column1 and column2 relative to the content and then remove column2? In addition, if both columns contain something, consider the content of column1 as the first choice. It's about SQLite. Please note that these are permanent changes to the database and column layout, not a JOIN for SELECT.

Input SQLite database:

|column1 | column2 |column_a|
|========|=========|========|
|"test"  |         |1       |
|"test2" |"test3"  |2       |
|        |"xxx"    |3       |

Pseudocode:

column1 = column1 column2 column2.delete()

Wyjściowa baza SQLite:

|column1 |column_a|
|========|========|
|"test"  |1       |
|"test2" |2       |
|"xxx"   |3       |

CodePudding user response：

This type of work needs to be done in a few steps.

1. Use the query from @Tim Biegeleisen to create a new table. Say tab_dev (To be safe, you should use CREATE TABLE and then INSERT INTO to make sure column types, constraint...etc are created to specs).

create table tab_dev
as
select COALESCE(column1, column2) as column1,
       columna
  from tab_prod;

At this moment, you should have the old version tab_prod and new version tab_dev. Carefully review the new table against the old one before proceed to next step.

2. Rename the old table to some backup table first (DO NOT DELETE it at this moment).

alter table tab_prod rename to tab_prod_backup;

3. Now the old version table's name is available, so rename the new version tab_dev to tab_prod.

alter table tab_dev rename to tab_prod;

4. Carefully verify and validate data in tab_prod if everything goes as planned. Delete the backup table tab_prod_backup as needed.

CodePudding user response：

We can use COALESCE() here:

SELECT COALESCE(column1, column2) AS column1,
       column_a
FROM yourTable;

CodePudding user response：

Let's assume the table name is myTable. Here's the DDL for your question:

DROP TABLE IF EXISTS myTable;
CREATE TABLE IF NOT EXISTS myTable(column1, column2, column_a);
INSERT INTO myTable VALUES("test", null, 1);
INSERT INTO myTable VALUES("test2", "test3", 2);
INSERT INTO myTable VALUES(null, "xxx", 3);

Let's assume that the question was about UPDATE followed by SELECT. It appears that we want to copy column2 into column1 to fill the NULL values only. The non-NULL can stay as is. Then, the subsequent SELECT statement seems only interested in column1 and column_a so we can exclude column2 from the SELECT:

UPDATE myTable
SET column1 = column2
WHERE column1 IS NULL;

SELECT column1, column_a
FROM myTable;

It is possible that some of the assumptions above may not be correct. For example, the question may not be permitting the source table use UPDATE. For which the SELECT statement required would be more complex. Then there is the matter of SQLite version since different versions have different functions that would have helped, such as IFF. Also, it is not clear whether column2 needed to be dropped. For that, a later version of SQLite is required to alter an existing table, else, we would have to recreate the table with column2 removed.