From http://blog.cartodb.com/faster-data-updates-with-cartodb/

update_and_insert.sql

WITH
-- write the new values
new(the_geom,description,name) AS (
  VALUES (ST_SetSRID(ST_Point(12,12),4326),'an update',123456),
         (ST_SetSRID(ST_Point(13,13),4326),'an insert',100000)
),
-- update existing rows
upsert AS (
  UPDATE {table} original
  SET the_geom=new.the_geom, description=new.description
  FROM new WHERE original.name = new.name
  RETURNING original.name
)
-- insert missing rows
INSERT INTO {table} (the_geom,description,name)
SELECT new.the_geom, new.description, new.name FROM new
WHERE new.name NOT IN (
  SELECT name FROM upsert
);