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 ARTICLENUMBER
s that are not in B
.
Related Topics
Oracle Convert Timestamp with Timezone to Date
Auditing SQL Server Data Changes
SQL Pivot with Multiple Columns
MySQL Bulk Load Command Line Tool
Crosstab with a Large or Undefined Number of Categories
How to Do Select Unique with Linq
Select Max(X) Is Returning Null; How to Make It Return 0
Swap Unique Indexed Column Values in Database
How to Execute a Stored Procedure Inside a Select Query
How to Return Default Value from SQL Query
How to Execute SQL Query Without Displaying Results
SQL Table Aliases - Good or Bad
How to Get the Last Day of Month in Postgres
Is There a Severe Performance Hit for Using Foreign Keys in SQL Server
Editing Record Issues in Access/SQL (Write Conflict)
Decode( ) Function in SQL Server