{"action":"created","issue":{"url":"https://api.github.com/repos/robconery/massive-js/issues/334","repository_url":"https://api.github.com/repos/robconery/massive-js","labels_url":"https://api.github.com/repos/robconery/massive-js/issues/334/labels{/name}","comments_url":"https://api.github.com/repos/robconery/massive-js/issues/334/comments","events_url":"https://api.github.com/repos/robconery/massive-js/issues/334/events","html_url":"https://github.com/robconery/massive-js/issues/334","id":191590362,"number":334,"title":"Syntax for updating multiple rows with different data","user":{"login":"Aedaeum","id":2054218,"avatar_url":"https://avatars.githubusercontent.com/u/2054218?v=3","gravatar_id":"","url":"https://api.github.com/users/Aedaeum","html_url":"https://github.com/Aedaeum","followers_url":"https://api.github.com/users/Aedaeum/followers","following_url":"https://api.github.com/users/Aedaeum/following{/other_user}","gists_url":"https://api.github.com/users/Aedaeum/gists{/gist_id}","starred_url":"https://api.github.com/users/Aedaeum/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Aedaeum/subscriptions","organizations_url":"https://api.github.com/users/Aedaeum/orgs","repos_url":"https://api.github.com/users/Aedaeum/repos","events_url":"https://api.github.com/users/Aedaeum/events{/privacy}","received_events_url":"https://api.github.com/users/Aedaeum/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":7,"created_at":"2016-11-24T20:58:31Z","updated_at":"2016-11-25T18:05:27Z","closed_at":null,"body":"Example raw posgres query:\r\n```\r\nupdate users as u set -- postgres FTW\r\n  email = u2.email,\r\n  first_name = u2.first_name,\r\n  last_name = u2.last_name\r\nfrom (values\r\n  (1, 'hollis@weimann.biz', 'Hollis', 'O\\'Connell'),\r\n  (2, 'robert@duncan.info', 'Robert', 'Duncan')\r\n) as u2(id, email, first_name, last_name)\r\nwhere u2.id = u.id;\r\n```\r\n\r\nIs there a way to do this using massivejs syntax without having to write the raw sql query like above?"},"comment":{"url":"https://api.github.com/repos/robconery/massive-js/issues/comments/263006121","html_url":"https://github.com/robconery/massive-js/issues/334#issuecomment-263006121","issue_url":"https://api.github.com/repos/robconery/massive-js/issues/334","id":263006121,"user":{"login":"dmfay","id":3075455,"avatar_url":"https://avatars.githubusercontent.com/u/3075455?v=3","gravatar_id":"","url":"https://api.github.com/users/dmfay","html_url":"https://github.com/dmfay","followers_url":"https://api.github.com/users/dmfay/followers","following_url":"https://api.github.com/users/dmfay/following{/other_user}","gists_url":"https://api.github.com/users/dmfay/gists{/gist_id}","starred_url":"https://api.github.com/users/dmfay/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dmfay/subscriptions","organizations_url":"https://api.github.com/users/dmfay/orgs","repos_url":"https://api.github.com/users/dmfay/repos","events_url":"https://api.github.com/users/dmfay/events{/privacy}","received_events_url":"https://api.github.com/users/dmfay/received_events","type":"User","site_admin":false},"created_at":"2016-11-25T18:05:27Z","updated_at":"2016-11-25T18:05:27Z","body":"In short: you don't. Even the raw query you posted is iterating over the temporary object created by `VALUES`; it's just performing the iteration as close as possible to the data to minimize roundtrip time. This can't be abstracted out without either increasing write time or doing redundant work to batch the data. The work _must_ happen in the database engine in order to realize best performance.\r\n\r\nUsing `COPY FROM` to stream your logfile into temporary storage and updating your actual table from there like I suggested would keep the iteration in the db engine and leverage your PK/FK indexing. You should be able to do that with a script or database function, since Massive lets you invoke those directly. But as John said, any solution that involves using a data mapper or an O/R mapper to build and emit an `UPDATE` statement or statements will be suboptimal: mappers are a 99% solution, and this is a 1% problem."}}
{"actor":{"display_login":"dmfay"}}