I have two tables, one stores customer orders and the other stores items in a specific order. I am trying to update both these tables at once as follows:
const updateCustOrder = 'UPDATE orders SET customer=$1, due_date=$2 WHERE order_id=$3';
const updateItem = 'UPDATE items SET order_id=$1, quantity=$2, cost=$3 WHERE item_id=$4';
Promise.all([
pool.query(updateCustOrder, [customer, due_date, order_id]),
pool.query(updateItem, [order_id, qty, cost, item_id])
]).then(function() {
response.status(201).send('Successfully Updated');
}).catch(function (e) {
response.status(500).send('Updated failed');
});
The attribute order_id is a primary key in the orders table and a foreign key in the items table. The problem I'm running into is that the values in the orders table get successfully updated, but the values in the items table are not updated.
Have you looked into knex at all? It can help a lot with the more painful SQL tasks. Though, that doesn't exactly answer your question.
You could try using async/await.
I admit I'm a bit rusty in Node. But, something like this should work:
async function sqlCalls() {
const updateCustOrder = 'UPDATE orders SET customer=$1, due_date=$2 WHERE order_id=$3';
const updateItem = 'UPDATE items SET order_id=$1, quantity=$2, cost=$3 WHERE item_id=$4';
const first = await pool.query(updateCustOrder, [customer, due_date, order_id]);
const second = await pool.query(updateItem, [order_id, qty, cost, item_id])
// Use If/Else for error handling
}