根据另外两个表的记录更新表

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.

现在,我想从表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.

从这些记录中,如果afm,ama在两个表中都存在Requests和Tax_data更新状态为有效,否则如果它们仅存在于Requests而不是Tax_data更新状态中则无效。

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.

我想从StatusTable中选择(1个待处理的),(4个,待处理的),如果来自请求的(1 01 001)与来自Tax_data的(01 001)相同,则将状态从挂起更改为有效。如果它(04 008)与(04 008)不同,则将状态从挂起更改为无效。

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

我怎么能用php和mysql做到这一点?有任何想法吗?我觉得有点复杂。

1 个解决方案

#1

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

如果我正确理解您的问题,您可以使用带有case语句的外连接来确定新状态:

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'

#1