1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/db-sql.R
\name{sql_query_insert}
\alias{sql_query_insert}
\alias{sql_query_append}
\alias{sql_query_update_from}
\alias{sql_query_upsert}
\alias{sql_query_delete}
\title{Generate SQL for Insert, Update, Upsert, and Delete}
\usage{
sql_query_insert(
con,
x_name,
y,
by,
...,
conflict = c("error", "ignore"),
returning_cols = NULL,
method = NULL
)
sql_query_append(con, x_name, y, ..., returning_cols = NULL)
sql_query_update_from(
con,
x_name,
y,
by,
update_values,
...,
returning_cols = NULL
)
sql_query_upsert(
con,
x_name,
y,
by,
update_cols,
...,
returning_cols = NULL,
method = NULL
)
sql_query_delete(con, x_name, y, by, ..., returning_cols = NULL)
}
\arguments{
\item{con}{Database connection.}
\item{x_name}{Name of the table to update.}
\item{y}{A lazy tbl.}
\item{by}{An unnamed character vector giving the key columns. The key columns
must exist in both \code{x} and \code{y}. Keys typically uniquely identify each row,
but this is only enforced for the key values of \code{y} when \code{rows_update()},
\code{rows_patch()}, or \code{rows_upsert()} are used.
By default, we use the first column in \code{y}, since the first column is
a reasonable place to put an identifier variable.}
\item{...}{Other parameters passed onto methods.}
\item{conflict}{For \code{rows_insert()}, how should keys in \code{y} that conflict
with keys in \code{x} be handled? A conflict arises if there is a key in \code{y}
that already exists in \code{x}.
One of:
\itemize{
\item \code{"error"}, the default, will error if there are any keys in \code{y} that
conflict with keys in \code{x}.
\item \code{"ignore"} will ignore rows in \code{y} with keys that conflict with keys in
\code{x}.
}}
\item{returning_cols}{Optional. Names of columns to return.}
\item{method}{Optional. The method to use.}
\item{update_values}{A named SQL vector that specify how to update the columns.}
\item{update_cols}{Names of columns to update.}
}
\value{
A SQL query.
}
\description{
These functions generate the SQL used in \code{rows_*(in_place = TRUE)}.
}
\details{
Insert Methods
\subsection{\code{"where_not_exists"}}{
The default for most databases.
\if{html}{\out{<div class="sourceCode">}}\preformatted{INSERT INTO x_name
SELECT *
FROM y
WHERE NOT EXISTS <match on by columns>
}\if{html}{\out{</div>}}
}
\subsection{\code{"on_conflict"}}{
Supported by:
\itemize{
\item Postgres
\item SQLite
}
This method uses the \verb{ON CONFLICT} clause and therefore requires a unique
index on the columns specified in \code{by}.
}
Upsert Methods
\subsection{\code{"merge"}}{
The upsert method according to the SQL standard. It uses the \code{MERGE} statement
\if{html}{\out{<div class="sourceCode">}}\preformatted{MERGE INTO x_name
USING y
ON <match on by columns>
WHEN MATCHED THEN
UPDATE SET ...
WHEN NOT MATCHED THEN
INSERT ...
}\if{html}{\out{</div>}}
}
\subsection{\code{"on_conflict"}}{
Supported by:
\itemize{
\item Postgres
\item SQLite
}
This method uses the \verb{ON CONFLICT} clause and therefore requires a unique
index on the columns specified in \code{by}.
}
\subsection{\code{"cte_update"}}{
Supported by:
\itemize{
\item Postgres
\item SQLite
\item Oracle
}
The classical way to upsert in Postgres and SQLite before support for
\verb{ON CONFLICT} was added. The update is done in a CTE clause and the unmatched
values are then inserted outside of the CTE.
}
}
\examples{
lf <- lazy_frame(
carrier = c("9E", "AA"),
name = c("Endeavor Air Inc.", "American Airlines Inc."),
con = simulate_postgres()
)
sql_query_upsert(
simulate_postgres(),
ident("airlines"),
lf,
by = "carrier",
update_cols = "name"
)
}
|
