CODEWITHSUNDEEP
postgresql
user2582312
user2582312

Reputation: 1

how to check the values in both tables were same or not within postgresql?

suppose if i am having two table likes leavedata and leavestatus in which some related columns were there (e.g. empid, refno), now i want to make the make some alterations in the leavedata table for some other columns (i.e. status). how to create a function by checking the values for both empid and refno in both tables for updating the status column

sample data

leavedata table

empid    refno     status  no.of days
101        1        pending      4



leavestatus table

empid    refno      check
101          1        null

Upvotes: 0

Views: 100

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

It sounds a bit like you might want UPDATE ... FROM, but it's hard to say given the sparse detail.

Something like:

UPDATE leavedata
SET status = 'approved'
FROM leavestatus
WHERE leavestatus.empid = leavedata.empid 
  AND leavestatus.refno = leavedata.refno
  AND leavestatus."check" IS NOT NULL;

Massage as appropriate; you didn't define the meanings of leavedata.check, etc so I'm hand-waving around those. DO NOT RUN THIS STATEMENT UNALTERED; use it as a guide to understand what you need to do, and remember: Always make backups and test statements inside a transaction so you can ROLLBACK.

By the way, "check" is a terrible column name; it's a reserved word in the SQL standard, and should not be used as an identifier. You'll have to "double quote" it everywhere.

In future try to describe what you're trying to achieve a bit more, and if possible provide more complete sample data in an SQLFiddle like this but with more INSERTs.

Upvotes: 1

Related Questions