CODEWITHSUNDEEP
phpmysql
simpleman91
simpleman91

Reputation: 3

Update a table according records of two other tables

I've created the according tables: 

CREATE TABLE IF NOT EXISTS StatusTable( 
    form_num INT not null auto_increment,  
    status VARCHAR(20) NOT NULL,
    primary key (form_num),
    FOREIGN KEY Status(form_num) REFERENCES Requests(reg_num)

CREATE TABLE IF NOT EXISTS Requests( 
    reg_num INT not null auto_increment, 
    ama VARCHAR(20) NOT NULL, 
    afm VARCHAR(9) NOT NULL, 
    description VARCHAR(100) NOT NULL, 
    est_cost FLOAT NOT NULL,
    primary key(reg_num),
    FOREIGN KEY Requests(afm) REFERENCES Citizens(afm)

CREATE TABLE IF NOT EXISTS Tax_data( 
    afm VARCHAR(9) NOT NULL primary key,
    ama VARCHAR(20) NOT NULL

Now, I want to choose the rows with a specific value for status from the table StatusTable.

From these records if afm, ama exist in both tables Requests and Tax_data update status to valid, else if they exist only in Requests and not in Tax_data update status to invalid.

For example:

StatusTable |   Requests |  Tax_data |
1  pending  | 1 01 001...|   01 001  |
2  valid    | 2 02 002...|   02 002  |
3  invalid  | 3 03 003...|   03 004  |
4  pending  | 4 04 004...|   04 008  |

I want to select (1 pending), (4, pending) from StatusTable and if (1 01 001) from Requests is the same as (01 001) from Tax_data change the status from pending to valid. If it is different like (4 04 004) from (04 008) change status from pending to invalid.

How can I do it with php and mysql? any ideas? I feel a little complicated.

Upvotes: 0

Views: 44

Answers (1)

sgeddes
sgeddes

Reputation: 62851

If I'm understanding your question correctly, you can use an outer join with a case statement to determine the new status:

select r.reg_num, s.status, r.ama, r.afm, t.ama, t.afm,
       case when t.afm is not null then 'Valid' 
            else 'Invalid'  
       end newstatus
from statustable s
    join requests r on s.form_num = r.req_num
    left join tax_data t on t.ama = r.ama and t.amf = r.amf
where s.status = 'Pending'

If you then need to update the status table, this should work:

update statustable s
join requests r on s.form_num = r.req_num
left join tax_data t on t.ama = r.ama and t.amf = r.amf
set s.status = 
       case when t.afm is not null then 'Valid' 
            else 'Invalid'  
       end
where s.status = 'Pending'

Upvotes: 1

Related Questions