如何在SQLite3中通过两张表创建新表或合并指定数据？

Got it, let's walk through two solid solutions for your SQLite3 scenario—either creating a brand new table that combines your Layer geometry data with the filtered Data records, or updating your existing Layer table to include the Data values. Both are straightforward, depending on your end goal.

If you want a separate table that holds both the geometry from Layer and the business data from Data (where j=1), use a CREATE TABLE ... AS SELECT query. Since you mentioned the filtered Data records (5 rows) match the number of rows in Layer, we can use row numbers to align them if there's no natural shared key (like an id column).

Example Query: #

CREATE TABLE Combined_Geometry_Data AS
SELECT
    l.*,  -- Includes all geometry columns from Layer
    d.business_col1,  -- Replace with actual Data columns you need
    d.business_col2
FROM (
    -- Add row numbers to Layer to align with filtered Data rows
    SELECT *, ROW_NUMBER() OVER () AS row_num
    FROM Layer
) l
JOIN (
    -- Filter Data to j=1 and add row numbers
    SELECT *, ROW_NUMBER() OVER () AS row_num
    FROM Data
    WHERE j = 1
) d ON l.row_num = d.row_num;

Notes: #

• If your tables do have a shared key (e.g., a matching id column), skip the row number subqueries and join directly on that key instead—it's more reliable.
• Replace business_col1 etc. with the actual columns from Data you want to include in the new table.

If you'd rather add the Data values directly to your existing Layer table, you'll first need to make sure Layer has columns to store those values. Then you can update the rows to match the filtered Data records.

Step 1: Add Columns to Layer (if missing) #

First, add columns to Layer that match the data types of the fields you want from Data:

-- Adjust column names and types to match your Data table
ALTER TABLE Layer ADD COLUMN business_col1 TEXT;
ALTER TABLE Layer ADD COLUMN business_col2 INTEGER;

Step 2: Update Layer with Filtered Data #

Again, using row numbers to align rows if there's no shared key:

WITH Layer_Rows AS (
    SELECT *, ROW_NUMBER() OVER () AS row_num
    FROM Layer
),
Filtered_Data AS (
    SELECT *, ROW_NUMBER() OVER () AS row_num
    FROM Data
    WHERE j = 1
)
UPDATE Layer_Rows
SET
    business_col1 = Filtered_Data.business_col1,
    business_col2 = Filtered_Data.business_col2
FROM Filtered_Data
WHERE Layer_Rows.row_num = Filtered_Data.row_num;

For Older SQLite Versions (pre-3.25.0) #

If your SQLite version doesn't support window functions (introduced in 3.25.0), you can generate row numbers using a subquery instead:

-- Update query without window functions
UPDATE Layer
SET
    business_col1 = (
        SELECT business_col1 FROM Data WHERE j=1 AND rowid = (
            SELECT COUNT(*) FROM Layer l2 WHERE l2.rowid <= Layer.rowid
        )
    ),
    business_col2 = (
        SELECT business_col2 FROM Data WHERE j=1 AND rowid = (
            SELECT COUNT(*) FROM Layer l2 WHERE l2.rowid <= Layer.rowid
        )
    );

Critical Notes: #

• Always back up your database before making schema changes or bulk updates—better safe than sorry!
• Double-check that the data types of the columns you add to Layer match the corresponding columns in Data to avoid type mismatch errors.

内容的提问来源于stack exchange，提问作者Mustafa Uçar