Created
July 8, 2018 18:15
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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