Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to do many updates in a single query WAY faster
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;
@jatorre

This comment has been minimized.

Copy link
Owner Author

commented Oct 15, 2012

Written by @strk.

Trying to update 3100 with many UPDATE statements took 27 seconds in CartoDB SQL API. Using this form it took 0.2seconds.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.