CODEWITHSUNDEEP
phpsqlpostgresql
user1364769
user1364769

Reputation: 217

PostgreSQL Query and PHP Assistance

Directly under this small intro here you'll see the layout of the database tables that I'm working with and then you'll see the details of my question. Please provide as much guidance as possible. I am still learning PHP and SQL and I really do appreciate your help as I get the hang of this.

Table One ('bue') -- chp_cd rgn_no bgu_cd work_state

Table Two ('chapterassociation') -- chp_cd rgn_no bgu_cd work_state

Database Type: PostgreSQL

I'm trying to do the following with these two tables, and I think it's a JOIN that I have to do but I'm not all that familiar with it and I'm trying to learn. I've created a query thus far to select a set of data from these tables so that the query isn't run on the entire database. Now with the data selected, I'm trying to do the following...

First and foremost, 'work_state' of table one ('bue') should be checked against 'work_state' of table two ('chapterassociation'). Once a match is found, 'bgu_cd' of table one ('bue') should be matched against 'bgu_cd' of table two ('chapterassociation'). When both matches are found, it will always point to a unique row within the second table ('chapterassociation'). Using that unique row within the second table ('chapterassociation'), the values of 'rgn_no' and 'chp_cd' should be UPDATED within the first table ('bue') to match the values within the second table ('chapterassociation').

I know this is probably asking a lot, but if someone could help me to construct a query to do this, it'd be wonderful! I really do want to learn, as I don't wish to be ignorant to this forever. Though I'm not sure if I completely understand how the JOIN and comparison here would work.

If I'm correct, I'll have to put this into seperate queries which will then be in PHP. So for example, it'll probably be a few IF ELSE statements that end with the final result of the final query, which updates the values from table two to table one.

Upvotes: 4

Views: 155

Answers (1)

MatBailie
MatBailie

Reputation: 86716

A JOIN will do both level of matching for you...

  bue
INNER JOIN
  chapterassociation
    ON  bue.work_state = chapterassociation.work_state
    AND bue.bgu_cd     = chapterassociation.bgu_cd

The actual algorithm is determined by PostreSQL. It could be a merge, use hashes, etc, and depends on indexes and other statistics about the data. But you don't need to worry about that directly, SQL abstracts that away for you.

Then you just need a mechanism to write the data from one table to the other...

UPDATE
  bue
SET
  rgn_no = chapterassociation.rgn_no,
  chp_cd = chapterassociation.chp_cd
FROM
  chapterassociation
WHERE bue.work_state = chapterassociation.work_state
  AND bue.bgu_cd     = chapterassociation.bgu_cd

Upvotes: 3

Related Questions