github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/update_from (about) 1 exec-ddl 2 CREATE TABLE abc (a int primary key, b int, c int) 3 ---- 4 5 exec-ddl 6 CREATE TABLE new_abc (a int, b int, c int) 7 ---- 8 9 # Test a self join. 10 opt 11 UPDATE abc SET b = other.b + 1, c = other.c + 1 FROM abc AS other WHERE abc.a = other.a 12 ---- 13 update abc 14 ├── columns: <none> 15 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 16 ├── update-mapping: 17 │ ├── b_new:10 => abc.b:2 18 │ └── c_new:11 => abc.c:3 19 └── project 20 ├── columns: b_new:10 c_new:11 abc.a:4!null abc.b:5 abc.c:6 other.a:7!null other.b:8 other.c:9 21 ├── inner-join (merge) 22 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 other.a:7!null other.b:8 other.c:9 23 │ ├── left ordering: +4 24 │ ├── right ordering: +7 25 │ ├── scan abc 26 │ │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 27 │ │ └── ordering: +4 28 │ ├── scan other 29 │ │ ├── columns: other.a:7!null other.b:8 other.c:9 30 │ │ └── ordering: +7 31 │ └── filters (true) 32 └── projections 33 ├── other.b:8 + 1 [as=b_new:10] 34 └── other.c:9 + 1 [as=c_new:11] 35 36 # Test when Update uses multiple tables. 37 opt 38 UPDATE abc SET b = other.b, c = other.c FROM new_abc AS other WHERE abc.a = other.a 39 ---- 40 update abc 41 ├── columns: <none> 42 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 43 ├── update-mapping: 44 │ ├── other.b:8 => abc.b:2 45 │ └── other.c:9 => abc.c:3 46 └── distinct-on 47 ├── columns: abc.a:4!null abc.b:5 abc.c:6 other.a:7!null other.b:8 other.c:9 rowid:10!null 48 ├── grouping columns: abc.a:4!null 49 ├── inner-join (hash) 50 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 other.a:7!null other.b:8 other.c:9 rowid:10!null 51 │ ├── scan abc 52 │ │ └── columns: abc.a:4!null abc.b:5 abc.c:6 53 │ ├── scan other 54 │ │ └── columns: other.a:7 other.b:8 other.c:9 rowid:10!null 55 │ └── filters 56 │ └── abc.a:4 = other.a:7 57 └── aggregations 58 ├── first-agg [as=abc.b:5] 59 │ └── abc.b:5 60 ├── first-agg [as=abc.c:6] 61 │ └── abc.c:6 62 ├── first-agg [as=other.a:7] 63 │ └── other.a:7 64 ├── first-agg [as=other.b:8] 65 │ └── other.b:8 66 ├── first-agg [as=other.c:9] 67 │ └── other.c:9 68 └── first-agg [as=rowid:10] 69 └── rowid:10 70 71 # Check if UPDATE FROM works well with RETURNING expressions that reference the FROM tables. 72 opt 73 UPDATE abc 74 SET 75 b = old.b + 1, c = old.c + 2 76 FROM 77 abc AS old 78 WHERE 79 abc.a = old.a 80 RETURNING 81 abc.a, abc.b AS new_b, old.b as old_b, abc.c as new_c, old.c as old_c 82 ---- 83 update abc 84 ├── columns: a:1!null new_b:2 old_b:8 new_c:3 old_c:9 85 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 86 ├── update-mapping: 87 │ ├── b_new:10 => abc.b:2 88 │ └── c_new:11 => abc.c:3 89 └── project 90 ├── columns: b_new:10 c_new:11 abc.a:4!null abc.b:5 abc.c:6 old.b:8 old.c:9 91 ├── inner-join (merge) 92 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 old.a:7!null old.b:8 old.c:9 93 │ ├── left ordering: +4 94 │ ├── right ordering: +7 95 │ ├── scan abc 96 │ │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 97 │ │ └── ordering: +4 98 │ ├── scan old 99 │ │ ├── columns: old.a:7!null old.b:8 old.c:9 100 │ │ └── ordering: +7 101 │ └── filters (true) 102 └── projections 103 ├── old.b:8 + 1 [as=b_new:10] 104 └── old.c:9 + 2 [as=c_new:11] 105 106 # Check if RETURNING * returns everything 107 opt 108 UPDATE abc SET b = old.b + 1, c = old.c + 2 FROM abc AS old WHERE abc.a = old.a RETURNING * 109 ---- 110 update abc 111 ├── columns: a:1!null b:2 c:3 a:7 b:8 c:9 112 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 113 ├── update-mapping: 114 │ ├── b_new:10 => abc.b:2 115 │ └── c_new:11 => abc.c:3 116 └── project 117 ├── columns: b_new:10 c_new:11 abc.a:4!null abc.b:5 abc.c:6 old.a:7!null old.b:8 old.c:9 118 ├── inner-join (merge) 119 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 old.a:7!null old.b:8 old.c:9 120 │ ├── left ordering: +4 121 │ ├── right ordering: +7 122 │ ├── scan abc 123 │ │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 124 │ │ └── ordering: +4 125 │ ├── scan old 126 │ │ ├── columns: old.a:7!null old.b:8 old.c:9 127 │ │ └── ordering: +7 128 │ └── filters (true) 129 └── projections 130 ├── old.b:8 + 1 [as=b_new:10] 131 └── old.c:9 + 2 [as=c_new:11] 132 133 # Check if the joins are optimized (check if the filters are pushed down). 134 opt 135 UPDATE abc SET b = old.b + 1, c = old.c + 2 FROM abc AS old WHERE abc.a = old.a AND abc.a = 2 136 ---- 137 update abc 138 ├── columns: <none> 139 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 140 ├── update-mapping: 141 │ ├── b_new:10 => abc.b:2 142 │ └── c_new:11 => abc.c:3 143 └── project 144 ├── columns: b_new:10 c_new:11 abc.a:4!null abc.b:5 abc.c:6 old.a:7!null old.b:8 old.c:9 145 ├── inner-join (cross) 146 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 old.a:7!null old.b:8 old.c:9 147 │ ├── scan abc 148 │ │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 149 │ │ └── constraint: /4: [/2 - /2] 150 │ ├── scan old 151 │ │ ├── columns: old.a:7!null old.b:8 old.c:9 152 │ │ └── constraint: /7: [/2 - /2] 153 │ └── filters (true) 154 └── projections 155 ├── old.b:8 + 1 [as=b_new:10] 156 └── old.c:9 + 2 [as=c_new:11] 157 158 # Update values of table from values expression 159 opt 160 UPDATE abc SET b = other.b, c = other.c FROM (values (1, 2, 3), (2, 3, 4)) as other ("a", "b", "c") WHERE abc.a = other.a 161 ---- 162 update abc 163 ├── columns: <none> 164 ├── fetch columns: a:4 b:5 c:6 165 ├── update-mapping: 166 │ ├── column2:8 => b:2 167 │ └── column3:9 => c:3 168 └── distinct-on 169 ├── columns: a:4!null b:5 c:6 column1:7!null column2:8!null column3:9!null 170 ├── grouping columns: a:4!null 171 ├── inner-join (lookup abc) 172 │ ├── columns: a:4!null b:5 c:6 column1:7!null column2:8!null column3:9!null 173 │ ├── key columns: [7] = [4] 174 │ ├── lookup columns are key 175 │ ├── values 176 │ │ ├── columns: column1:7!null column2:8!null column3:9!null 177 │ │ ├── (1, 2, 3) 178 │ │ └── (2, 3, 4) 179 │ └── filters (true) 180 └── aggregations 181 ├── first-agg [as=b:5] 182 │ └── b:5 183 ├── first-agg [as=c:6] 184 │ └── c:6 185 ├── first-agg [as=column1:7] 186 │ └── column1:7 187 ├── first-agg [as=column2:8] 188 │ └── column2:8 189 └── first-agg [as=column3:9] 190 └── column3:9 191 192 # Check if UPDATE ... FROM works with multiple tables. 193 exec-ddl 194 CREATE TABLE ab (a INT, b INT) 195 ---- 196 197 exec-ddl 198 CREATE TABLE ac (a INT, c INT) 199 ---- 200 201 opt 202 UPDATE abc SET b = ab.b, c = ac.c FROM ab, ac WHERE abc.a = ab.a AND abc.a = ac.a 203 ---- 204 update abc 205 ├── columns: <none> 206 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 207 ├── update-mapping: 208 │ ├── ab.b:8 => abc.b:2 209 │ └── ac.c:11 => abc.c:3 210 └── distinct-on 211 ├── columns: abc.a:4!null abc.b:5 abc.c:6 ab.a:7!null ab.b:8 ab.rowid:9!null ac.a:10!null ac.c:11 ac.rowid:12!null 212 ├── grouping columns: abc.a:4!null 213 ├── inner-join (hash) 214 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 ab.a:7!null ab.b:8 ab.rowid:9!null ac.a:10!null ac.c:11 ac.rowid:12!null 215 │ ├── scan ab 216 │ │ └── columns: ab.a:7 ab.b:8 ab.rowid:9!null 217 │ ├── inner-join (hash) 218 │ │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 ac.a:10!null ac.c:11 ac.rowid:12!null 219 │ │ ├── scan ac 220 │ │ │ └── columns: ac.a:10 ac.c:11 ac.rowid:12!null 221 │ │ ├── scan abc 222 │ │ │ └── columns: abc.a:4!null abc.b:5 abc.c:6 223 │ │ └── filters 224 │ │ └── abc.a:4 = ac.a:10 225 │ └── filters 226 │ └── ab.a:7 = abc.a:4 227 └── aggregations 228 ├── first-agg [as=abc.b:5] 229 │ └── abc.b:5 230 ├── first-agg [as=abc.c:6] 231 │ └── abc.c:6 232 ├── first-agg [as=ab.a:7] 233 │ └── ab.a:7 234 ├── first-agg [as=ab.b:8] 235 │ └── ab.b:8 236 ├── first-agg [as=ab.rowid:9] 237 │ └── ab.rowid:9 238 ├── first-agg [as=ac.a:10] 239 │ └── ac.a:10 240 ├── first-agg [as=ac.c:11] 241 │ └── ac.c:11 242 └── first-agg [as=ac.rowid:12] 243 └── ac.rowid:12 244 245 # Make sure UPDATE ... FROM works with LATERAL. 246 opt 247 UPDATE abc 248 SET 249 b=ab.b, c = other.c 250 FROM 251 ab, LATERAL 252 (SELECT * FROM ac WHERE ab.a=ac.a) AS other 253 WHERE 254 abc.a=ab.a 255 RETURNING 256 * 257 ---- 258 update abc 259 ├── columns: a:1!null b:2 c:3 a:7 b:8 a:10 c:11 260 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 261 ├── update-mapping: 262 │ ├── ab.b:8 => abc.b:2 263 │ └── ac.c:11 => abc.c:3 264 └── distinct-on 265 ├── columns: abc.a:4!null abc.b:5 abc.c:6 ab.a:7!null ab.b:8 ac.a:10!null ac.c:11 266 ├── grouping columns: abc.a:4!null 267 ├── inner-join (hash) 268 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 ab.a:7!null ab.b:8 ac.a:10!null ac.c:11 269 │ ├── scan ab 270 │ │ └── columns: ab.a:7 ab.b:8 271 │ ├── inner-join (hash) 272 │ │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 ac.a:10!null ac.c:11 273 │ │ ├── scan ac 274 │ │ │ └── columns: ac.a:10 ac.c:11 275 │ │ ├── scan abc 276 │ │ │ └── columns: abc.a:4!null abc.b:5 abc.c:6 277 │ │ └── filters 278 │ │ └── abc.a:4 = ac.a:10 279 │ └── filters 280 │ └── ab.a:7 = abc.a:4 281 └── aggregations 282 ├── first-agg [as=abc.b:5] 283 │ └── abc.b:5 284 ├── first-agg [as=abc.c:6] 285 │ └── abc.c:6 286 ├── first-agg [as=ab.a:7] 287 │ └── ab.a:7 288 ├── first-agg [as=ab.b:8] 289 │ └── ab.b:8 290 ├── first-agg [as=ac.a:10] 291 │ └── ac.a:10 292 └── first-agg [as=ac.c:11] 293 └── ac.c:11 294 295 # Make sure UPDATE ... FROM can return hidden columns. 296 opt 297 UPDATE abc 298 SET 299 b=ab.b, c = ac.c 300 FROM 301 ab, ac 302 WHERE 303 abc.a=ab.a AND abc.a = ac.a 304 RETURNING 305 *, ab.rowid, ac.rowid 306 ---- 307 update abc 308 ├── columns: a:1!null b:2 c:3 a:7 b:8 a:10 c:11 rowid:9 rowid:12 309 ├── fetch columns: abc.a:4 abc.b:5 abc.c:6 310 ├── update-mapping: 311 │ ├── ab.b:8 => abc.b:2 312 │ └── ac.c:11 => abc.c:3 313 └── distinct-on 314 ├── columns: abc.a:4!null abc.b:5 abc.c:6 ab.a:7!null ab.b:8 ab.rowid:9!null ac.a:10!null ac.c:11 ac.rowid:12!null 315 ├── grouping columns: abc.a:4!null 316 ├── inner-join (hash) 317 │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 ab.a:7!null ab.b:8 ab.rowid:9!null ac.a:10!null ac.c:11 ac.rowid:12!null 318 │ ├── scan ab 319 │ │ └── columns: ab.a:7 ab.b:8 ab.rowid:9!null 320 │ ├── inner-join (hash) 321 │ │ ├── columns: abc.a:4!null abc.b:5 abc.c:6 ac.a:10!null ac.c:11 ac.rowid:12!null 322 │ │ ├── scan ac 323 │ │ │ └── columns: ac.a:10 ac.c:11 ac.rowid:12!null 324 │ │ ├── scan abc 325 │ │ │ └── columns: abc.a:4!null abc.b:5 abc.c:6 326 │ │ └── filters 327 │ │ └── abc.a:4 = ac.a:10 328 │ └── filters 329 │ └── ab.a:7 = abc.a:4 330 └── aggregations 331 ├── first-agg [as=abc.b:5] 332 │ └── abc.b:5 333 ├── first-agg [as=abc.c:6] 334 │ └── abc.c:6 335 ├── first-agg [as=ab.a:7] 336 │ └── ab.a:7 337 ├── first-agg [as=ab.b:8] 338 │ └── ab.b:8 339 ├── first-agg [as=ab.rowid:9] 340 │ └── ab.rowid:9 341 ├── first-agg [as=ac.a:10] 342 │ └── ac.a:10 343 ├── first-agg [as=ac.c:11] 344 │ └── ac.c:11 345 └── first-agg [as=ac.rowid:12] 346 └── ac.rowid:12