CODEWITHSUNDEEP
phpmysql
Razorphyn
Razorphyn

Reputation: 1332

Update different columns in different tables based on column value

I'm trying to what is wrote in the question, probably is a stupid question but I can't find a solution, basically this is what I would like to do:

if(a.operator_id!=0){
    UPDATE ".$SupportTicketsTable." a ,".$SupportUserTable." b 
    SET a.title=? , a.priority=?, a.ticket_status=?, 
    b.solved_tickets= CASE 
        WHEN a.ticket_status!='0' THEN (b.solved_tickets+1) 
        ELSE b.solved_tickets END , 
    b.assigned_tickets= CASE  
        WHEN a.ticket_status!='0' THEN (b.assigned_tickets-1) 
        ELSE b.assigned_tickets END ,a.ticket_status='0' 
    WHERE a.enc_id=? AND b.id=a.operator_id
}
else{
    UPDATE ".$SupportTicketsTable." a ,".$SupportUserTable." b 
    SET a.title=? , a.priority=?, a.ticket_status=?
        ELSE b.assigned_tickets END ,a.ticket_status='0' 
    WHERE a.enc_id=?
}

When a.operator_id=0 there isn't a value for b.id that will match the condition Is there a way to do this in only one query? Thanks in advance

Upvotes: 0

Views: 63

Answers (1)

Alex
Alex

Reputation: 28

  1. I think that your branch ELSE is wrong:

    ...
else{
UPDATE ".$SupportTicketsTable." a ,".$SupportUserTable." b 
SET a.title=? , a.priority=?, a.ticket_status=?
    ELSE b.assigned_tickets END ,a.ticket_status='0' 
WHERE a.enc_id=?
}

  2. You can create one or two srored procedures with a dynamicSQL inside and put tablenames as a parameter into there. for example:

    DELIMITER $$
DROP PROCEDURE IF EXISTS my_proc $$
CREATE PROCEDURE my_proc (IN SupportTicketsTable VARCHAR(20), IN SupportUserTable VARCHAR(20))
BEGIN
SET @SQLS = CONCAT('UPDATE ',SupportTicketsTable, 'a ', SupportUserTable,' b 
    SET a.title=? , a.priority=?, a.ticket_status=?, 
    b.solved_tickets= CASE 
       WHEN a.ticket_status!='0' THEN (b.solved_tickets+1) 
       ELSE b.solved_tickets END , 
    b.assigned_tickets= CASE  
       WHEN a.ticket_status!='0' THEN (b.assigned_tickets-1) 
       ELSE b.assigned_tickets END ,
    a.ticket_status='0' 
    WHERE a.enc_id=? AND b.id=a.operator_id';

PREPARE STMT FROM @SQLS;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

END$$

Upvotes: 1

Related Questions