Skip to content

Instantly share code, notes, and snippets.

@kebman
Created July 8, 2018 18:15
Show Gist options
  • Save kebman/d9eb7a66dfe16b5b0fc7706f3ac53eff to your computer and use it in GitHub Desktop.
Save kebman/d9eb7a66dfe16b5b0fc7706f3ac53eff to your computer and use it in GitHub Desktop.
Short and simple example on how to build a dynamic update query in SQL using JavaScript
// data from database (illustration):
const selected = {
nick: "bob",
name: "Richard",
surname: "Bach",
address: "Somewhere"
};
// new input from client
let newInput = {
nick: "Bobby",
name: "Richard",
surname: "Bacchus",
address: "Somewhere"
};
/*
This is what it should end up as:
UPDATE users
SET email = ?, password = ?
WHERE id = ?
*/
// partial query build:
const part1 = "UPDATE users SET";
let part2 = "";
const part3 = "WHERE id = ?";
// placeholder for parameters:
let params = [];
// query builder:
for (let key in selected) {
if (selected[key] != newInput[key]) {
part2 += key + " = ?, ";
params.push(key);
}
}
params.push('id'); // ...and the tuple you want to update
// finished dynamic SQL query:
console.log(part1);
console.log(part2.substring(0,part2.length-2)); // snip last comma
console.log(part3);
// params to use:
console.log(params);
/*
Output:
UPDATE users SET
nick = ?, surname = ?
WHERE id = ?
[ 'nick', 'surname', 'id' ]
*/
// now input the update query into whatever API you use to communicate with your SQL database...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment