Sqlite Inner Join - Update Using Values from Another Table

SQLite inner join - update using values from another table

Using the update statement it is not possible because in sqlite joins in an update statement are not supported. See docs:
update statement

If you only wanted to update a single column to a static value, you could use a subquery in the update statement correctly. See this example: How do I make an UPDATE while joining tables on SQLite?

Now in your example, making an assumption that there is a unique key on "column f" - a workaround/solution I have come up with is using the replace statement:

replace into table2
(a, b, c, d, e, f, g)
select src.a, src.b, src.c, src.d, src.e, dest.f, dest.g
from table1 src
inner join table2 dest on src.f = dest.f

I also added an extra column to table2 "column g" to show how you'd "update" only some of the columns with this method.

One other thing to be cautious about is if you use "PRAGMA foreign_keys = ON;" it's possible to have issues with this as the row is effectively deleted and inserted.

sqlite: update column from join

I cannot quite remember (or explain intelligently) why solution 3 doesn't work but I think it's something like table1 in the subquery is not the "same" table1 being updated. (I have struggled with this in the past, and have developed habits to avoid it, no longer remember the reason).

I do know if you change

inner join table1 on table1.A = table2.A to
where table1.A = table2.A it will work.

This will also work:

update table1 
    set C = (
    select table2.D
    from table2
    inner join table1 t1 on t1.A = table2.A
    and t1.A = table1.A
    )

Both of these solutions will set C to null if there is no matching row in table2.

Update table values from another table with the same user name

As long as you have suitable indexes in place this should work alright:

UPDATE table_a
SET
      column_a_1 = (SELECT table_b.column_b_1 
                            FROM table_b
                            WHERE table_b.user_name = table_a.user_name )
    , column_a_2 = (SELECT table_b.column_b_2
                            FROM table_b
                            WHERE table_b.user_name = table_a.user_name )
WHERE
    EXISTS (
        SELECT *
        FROM table_b
        WHERE table_b.user_name = table_a.user_name
    )

UPDATE in sqlite3 did not support a FROM clause for a long time, which made this a little more work than in other RDBMS. UPDATE FROM was implemented in SQLite 3.33 however (2020-08-14) as mentioned at: https://stackoverflow.com/a/63079219/895245

If performance is not satisfactory, another option might be to build up new rows for table_a using a select and join with table_a into a temporary table. Then delete the data from table_a and repopulate from the temporary.

sqlite inner join update - 3 tables

You need to find id of row you want to update without using table on which update will be run. You could find those id's from only two tables 'table2' and 'table3'. Simple subquery will help you:

UPDATE table1 SET name = 0 
WHERE id_t2 IN (
    SELECT t2.id FROM table2 t2 
    INNER JOIN table3 t3 ON t2.id_t3 = t3.id 
    WHERE t3.name = 0
)

How do I make an UPDATE while joining tables on SQLite?

You can't. SQLite doesn't support JOINs in UPDATE statements.

But, you can probably do this with a subquery instead:

UPDATE closure SET checked = 0 
WHERE item_id IN (SELECT id FROM item WHERE ancestor_id = 1);

Or something like that; it's not clear exactly what your schema is.

SQLite - adding columns and updating them using another table

The ALTER TABLE statement does not support multiple columns, so you must add them in 2 separate statements:

ALTER TABLE Species ADD Name1 TEXT;
ALTER TABLE Species ADD ScientificName1 TEXT;

Your UPDATE statement is correct if your version of SQLite is 3.33.0+.

For previous versions (3.15.0+) you could use ROW VALUES:

UPDATE Species
SET (Name1, ScientificName1) = (
  SELECT temp.Name, temp.ScientificName
  FROM temp
  WHERE temp.Id = Species.Id
);

See the demo.

Update a table with data from other table with multiple conditions?

I suspect that you really want EXISTS -- that is to set all values in table A, with 1 if there is a non-NULL matching event. That would be:

UPDATE A
    SET NEWCOLUMN = (CASE WHEN EXISTS (SELECT 1
                                       FROM B
                                       WHERE b.ARTICLENUMBER = a.ARTICLENUMBER AND
                                             b.EVENT IS NOT NULL
                                        )
                          THEN 1 ELSE 0
                      END);

Note that this updates all rows in A -- even those with no matching article in B. As I say, I think this is what you want to do, although it is not exactly how your question is phrased. Your question does not specify what to do for ARTICLENUMBERs that are not in B.

---

Related Topics