I have a postgres db that contains the table events. I want a user to be able to update any column or columns of this table via input. The problem with the following code is that when a user updates, for example, the column wedding_name and the otherones do not recieve any value, my row is uptdated succesfully on the columns that received the new value. Howwever, the rest of the columns get empty because they are not recieving inputs. So I want to be able to update one columns, if possible on a sinle query withouth affecting the columns that are not touched. Thank you.
modifyEvent:async(req, res)=>{
try {
const eventId= req.params.eventId
const {weddingName,groom,bride,location,date,}=req.body
const updatedEvent=pool.query('UPDATE events SET wedding_name=$1,bride_name=$2,groom_name=$3,wedding_location=$4,wedding_date=$5 WHERE wedding_id=$6',[
weddingName,bride,groom,location,date,eventId
])
res.json(updatedEvent)
}
catch (error) {
console.log(error.message)
}
}
CodePudding user response:
You can try something like this :
const updatedEvent=pool.query('UPDATE events SET wedding_name=COALESCE($1, wedding_name), bride_name=COALESCE($2, bride_name), groom_name=COALESCE($3, groom_name), wedding_location=COALESCE($4, wedding_location), wedding_date=COALESCE($5, wedding_date) WHERE wedding_id=$6',[weddingName,bride,groom,location,date,eventId])