Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Forked from jatorre/gist:3892665
Last active December 18, 2015 02:58
Show Gist options
  • Save andrewxhill/5714809 to your computer and use it in GitHub Desktop.
Save andrewxhill/5714809 to your computer and use it in GitHub Desktop.
Efficient bulk update and insert

Bulk insert or update CartoDB records

Use the SQL example here to efficiently update CartoDB tables. See both the INSERT and UPDATE variants

INSERT INTO counties_results (pres_dem_pct, pres_gop_pct, pres_oth_pct, pres_pctrpt, st_usps, fips)( VALUES
(48.51, 39.69, 11.8, 100, 'AK', '02000'),
(51.65, 41.67, 6.68, 100 , 'AL' , '01001' ),
(52.14, 42.4, 5.46, 100 , 'AL' , '01003' ),
(51.94, 42.24, 5.82, 100 , 'AL' , '01005' ),
(60.28, 34.95, 4.76, 100 , 'AL' , '01007' ),
(53.19, 42.04, 4.77, 100 , 'AL' , '01009' ),
(45.07, 50.85, 4.08, 100 , 'AL' , '01011' ),
(45.78, 49.06, 5.16, 100 , 'AL' , '01013' ),
(57.94, 36.59, 5.47, 100 , 'AL' , '01015' ),
(46.98, 48.19, 4.83, 100 , 'AL' , '01017' ),
(57.38, 37.44, 5.18, 100 , 'AL' , '01019' ),
(51.15, 43.66, 5.19, 100 , 'AL' , '01021' ),
(48.13, 47.03, 4.84, 100 , 'AL' , '01023' ),
(54.74, 40.23, 5.03, 100 , 'AL' , '01025' ),
(48.85, 45.55, 5.59, 100 , 'AL' , '01027' ),
(43.34, 52.17, 4.5, 100 , 'AL' , '01029' ),
(54.65, 40.71, 4.64, 100 , 'AL' , '01031' ),
(53.65, 41.07, 5.28, 100 , 'AL' , '01033' ),
(57.72, 36.74, 5.55, 100 , 'AL' , '01035' ),
(44.02, 52.32, 3.66, 100 , 'AL' , '01037' ),
(57.02, 38.25, 4.74, 100 , 'AL' , '01039' )
)
UPDATE counties_results o SET pres_dem_pct = n.pres_dem_pct, pres_gop_pct = n.pres_gop_pct,
pres_oth_pct = n.pres_oth_pct, pres_pctrpt = n.pres_pctrpt
FROM ( VALUES
(48.51, 39.69, 11.8, 100, 'AK', '02000'),
(51.65, 41.67, 6.68, 100 , 'AL' , '01001' ),
(52.14, 42.4, 5.46, 100 , 'AL' , '01003' ),
(51.94, 42.24, 5.82, 100 , 'AL' , '01005' ),
(60.28, 34.95, 4.76, 100 , 'AL' , '01007' ),
(53.19, 42.04, 4.77, 100 , 'AL' , '01009' ),
(45.07, 50.85, 4.08, 100 , 'AL' , '01011' ),
(45.78, 49.06, 5.16, 100 , 'AL' , '01013' ),
(57.94, 36.59, 5.47, 100 , 'AL' , '01015' ),
(46.98, 48.19, 4.83, 100 , 'AL' , '01017' ),
(57.38, 37.44, 5.18, 100 , 'AL' , '01019' ),
(51.15, 43.66, 5.19, 100 , 'AL' , '01021' ),
(48.13, 47.03, 4.84, 100 , 'AL' , '01023' ),
(54.74, 40.23, 5.03, 100 , 'AL' , '01025' ),
(48.85, 45.55, 5.59, 100 , 'AL' , '01027' ),
(43.34, 52.17, 4.5, 100 , 'AL' , '01029' ),
(54.65, 40.71, 4.64, 100 , 'AL' , '01031' ),
(53.65, 41.07, 5.28, 100 , 'AL' , '01033' ),
(57.72, 36.74, 5.55, 100 , 'AL' , '01035' ),
(44.02, 52.32, 3.66, 100 , 'AL' , '01037' ),
(57.02, 38.25, 4.74, 100 , 'AL' , '01039' )
) AS n(pres_dem_pct, pres_gop_pct, pres_oth_pct, pres_pctrpt, st_usps, fips)
WHERE o.st_usps = n.st_usps AND o.fips = n.fips;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment