How can I update a table in a SQLite database?

Question

I want to perform multiple subsequent joins on tables that are locally stored in an SQLite database with R. I use this approach to keep my memory free from the data (stored as tibble), because some of the tables are larger than the size limit R allows for single vectors. While I manage to perform the joins and create a new table as a result from each join, I would rather like to update the existing table.

library(DBI)
library(dbplyr)
library(tidyverse)


con <- DBI::dbConnect(RSQLite::SQLite(), 
                      dbname = "test")

dbplyr::copy_nycflights13(con)

dbListTables(con)

Joining local tibbles works, but for my use case there won't be enough memory to perform this step in the environment.

# data stored locally
left_join(nycflights13::flights,
          nycflights13::planes,
          by = c("tailnum", "year")) |> 
  left_join(nycflights13::airlines, by = "carrier")

Data stored in a RSQLite database. First join works and returns df1, which is written as a new table to the database. For example:

# in dplyr /dbplyr
left_join(x = tbl(con, "flights"),
          y = tbl(con, "planes"),
          by = c("tailnum", "year")) |> 
  show_query() |> 
  compute(name = "df1", temporary = F)

Can't save second query, table df1 already exists. For example:

left_join(x = tbl(con, "df1"),
          y = tbl(con, "airlines"),
          by = "carrier") |> 
  show_query() |> 
  compute(name = "df1", temporary = F)

Is there a way to force compute() to overwrite an existing table? Or can someone give advice how to write that in SQL query? I tried the following:

query <- "
  UPDATE df1
  SET
    name = result.name
  FROM (
    SELECT
      carrier,
      name
    FROM
      df1
    LEFT JOIN
      airlines
    USING
      (carrier)
  ) AS result
  WHERE
    df1.carrier = result.carrier
"

# Execute the update query
dbExecute(con, query)

But I get the error: Error: no such table: df1; dbListTables(con) says that df1 is in the database.

What is wrong?

Answer 1

One problem you have is that you don't have name defined in df1 in the database; once you do, you can use an "update on join".

# setup
library(DBI); library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = "test")
dbplyr::copy_nycflights13(con)

# for the demo, we'll wipe out `df1` and regenerate it with an empty `name` column
dbExecute(con, "drop table df1")
left_join(x = tbl(con, "flights"),
          y = tbl(con, "planes"),
          by = c("tailnum", "year")) |> 
  mutate(name = NA_character_) |>
  show_query() |> 
  compute(name = "df1", temporary = FALSE)

From here, if we try to update with your existing query then we get an error:

Error: ambiguous column name: name

Further, once we fix that, we get a very slow update (I was never patient enough to let it go more than several minutes). Taking a cue from https://stackoverflow.com/a/21074659/3358272,

query <- "
UPDATE df1
SET name = (SELECT name
                  FROM airlines
                  WHERE airlines.carrier = df1.carrier) 
where EXISTS (SELECT name
                  FROM airlines
                  WHERE airlines.carrier = df1.carrier)"
dbExecute(con, query)
# [1] 336776

dbGetQuery(con, "select * from df1 limit 10") |> str()
# 'data.frame': 10 obs. of  27 variables:
#  $ year          : int  2013 2013 2013 2013 2013 2013 2013 2013 2013 2013
#  $ month         : int  1 1 1 1 1 1 1 1 1 1
#  $ day           : int  1 1 1 1 1 1 1 1 1 1
#  $ dep_time      : int  517 533 542 544 554 554 555 557 557 558
#  $ sched_dep_time: int  515 529 540 545 600 558 600 600 600 600
#  $ dep_delay     : num  2 4 2 -1 -6 -4 -5 -3 -3 -2
#  $ arr_time      : int  830 850 923 1004 812 740 913 709 838 753
#  $ sched_arr_time: int  819 830 850 1022 837 728 854 723 846 745
#  $ arr_delay     : num  11 20 33 -18 -25 12 19 -14 -8 8
#  $ carrier       : chr  "UA" "UA" "AA" "B6" ...
#  $ flight        : int  1545 1714 1141 725 461 1696 507 5708 79 301
#  $ tailnum       : chr  "N14228" "N24211" "N619AA" "N804JB" ...
#  $ origin        : chr  "EWR" "LGA" "JFK" "JFK" ...
#  $ dest          : chr  "IAH" "IAH" "MIA" "BQN" ...
#  $ air_time      : num  227 227 160 183 116 150 158 53 140 138
#  $ distance      : num  1400 1416 1089 1576 762 ...
#  $ hour          : num  5 5 5 5 6 5 6 6 6 6
#  $ minute        : num  15 29 40 45 0 58 0 0 0 0
#  $ time_hour     : num  1.36e+09 1.36e+09 1.36e+09 1.36e+09 1.36e+09 ...
#  $ type          : chr  NA NA NA NA ...
#  $ manufacturer  : chr  NA NA NA NA ...
#  $ model         : chr  NA NA NA NA ...
#  $ engines       : int  NA NA NA NA NA NA NA NA NA NA
#  $ seats         : int  NA NA NA NA NA NA NA NA NA NA
#  $ speed         : int  NA NA NA NA NA NA NA NA NA NA
#  $ engine        : chr  NA NA NA NA ...
#  $ name          : chr  "United Air Lines Inc." "United Air Lines Inc." "American Airlines Inc." "JetBlue Airways" ...

^{(Side note: this is denormalizing the df1 database; if that's your explicit intent, then it is doing what you mean, though in many database discussions it may be recommended/preferred to keep it normalized, where (for instance) the string "United Air Lines Inc." is stored in a 16-row table of carriers and names, and you join it into your data when you make the query. It's mostly a theoretical discussion, though storing df1.name in this way does cost a little more space in the database.)}