Home > front end >  Update multiple columns from input in postgres table using pg and express.js in a single query
Update multiple columns from input in postgres table using pg and express.js in a single query

Time:12-28

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])
  • Related