sqlite3 updating a table with data from another table only if 4 other columns match

Question

I do hope someone can help me out here. I researched a bit, but couldn't really find the exact answer I was looking for.

I have 2 tables:

Table1
======
Col1 | Col2 | Col3 | Col4 | Col5 | Col6

Table2
======
Col1 | Col2 | Col3 | Col4 | Col5 | Col6

Col5 and Col6 are empty in Table1 but have values in some entries of Table2. Both tables have the same entries in all other columns, but they are not ordered the same way.

The combination of the first 4 columns in each table is unique. I would like to copy the values in Table2.Col5 to Table1.Col5 where the unique combinations of the other 4 columns match between the two tables.

I am trying to do something similar to a VLOOKUP in excel, only searching for a matching combination of 4 columns, to retrieve the values of 2 other columns and update the relevant entries.

I did find something similar for SQL Server here: How do I UPDATE from a SELECT in SQL Server? but again, not what I need, especially since there the rows in both tables have the same order.

Any help will be much appreciated.

Answer 1

I assume you have a table structure like this:

CREATE TABLE Table1 (
  Col1 INT NOT NULL,
  Col2 INT NOT NULL,
  Col3 INT NOT NULL,
  Col4 INT NOT NULL,
  Col5 INT NOT NULL,
  Col6 INT NOT NULL,

  PRIMARY KEY (Col1, Col2, Col3, Col4)
);

CREATE TABLE Table2 (
  Col1 INT NOT NULL,
  Col2 INT NOT NULL,
  Col3 INT NOT NULL,
  Col4 INT NOT NULL,
  Col5 INT NOT NULL,
  Col6 INT NOT NULL,

  PRIMARY KEY (Col1, Col2, Col3, Col4)
);

And that you have sample data like this:

INSERT INTO Table1 (
  Col1, Col2, Col3, Col4, Col5, Col6
)
VALUES
  (1, 1, 1, 1, 25, 7),
  (2, 2, 2, 2, 8, 9),
  (3, 3, 3, 3, 25, 12);


INSERT INTO Table2 (
  Col1, Col2, Col3, Col4, Col5, Col6
)
VALUES
  (1, 1, 1, 1, 26, 8),
  (2, 2, 2, 2, 9, 10),
  (4, 4, 4, 4, 30, 3);

ANSI Solution

You can solve the problem using ANSI update syntax. The ANSI solution looks like this:

UPDATE Table1
SET
  Col5 = (
    SELECT Col5
    FROM Table2
    WHERE
      Table1.Col1 = Table2.Col1 AND
      Table1.Col2 = Table2.Col2 AND
      Table1.Col3 = Table2.Col3 AND
      Table1.Col4 = Table2.Col4
  ),
  Col6 = (
    SELECT Col6
    FROM Table2
    WHERE
      Table1.Col1 = Table2.Col1 AND
      Table1.Col2 = Table2.Col2 AND
      Table1.Col3 = Table2.Col3 AND
      Table1.Col4 = Table2.Col4
  )
WHERE EXISTS (
  SELECT *
  FROM Table2
  WHERE
    Table1.Col1 = Table2.Col1 AND
    Table1.Col2 = Table2.Col2 AND
    Table1.Col3 = Table2.Col3 AND
    Table1.Col4 = Table2.Col4
);

In the end, the data in Table1 looks like this:

Col1 Col2 Col3 Col4 Col5 Col6
1    1    1    1    26   8
2    2    2    2    9    10
3    3    3    3    25   12

Rows 1 and 2 have been updated because there are corresponding rows in Table2. Row 3 stays the same because there is no corresponding row in Table2.

See my SQLFiddle for a working example. I used the SQL.js implementation of SQLLite on SQLFiddle.

Frankly, the ANSI update syntax sucks. It sucks so badly that every commercial database engine has its own extended syntax to make complex updates easier.

I don't know if SQLite has its own extended syntax or has adopted that of another database engine.

SQL Server solution

You mentioned you tried the SQL Server solution.

Using SQL Server syntax the solution would look like this:

UPDATE Table1
SET
  Col5 = Table2.Col5,
  Col6 = Table2.Col6
FROM Table1
INNER JOIN Table2 ON
  Table1.Col1 = Table2.Col1 AND
  Table1.Col2 = Table2.Col2 AND
  Table1.Col3 = Table2.Col3 AND
  Table1.Col4 = Table2.Col4;

Unfortunately, SQLite doesn't understand it:

SQLite exception: 1, near "FROM": syntax error

The solution probably only works in SQL Server. The currently accepted answer to the linked question shows the different ways of solving a problem for SQL Server, Oracle, MySQL, and ANSI.