asked on

How to update multiple Mysql tables with one query statement

Please how do I update multiple tables with one query? I have the below query, but I'm getting an error:-

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION 
        UPDATE daily SET notify='0' WHERE username1='name' UNION 
' at line 1 in C:\wamp64\www\r

Open in new window

Query

$zero = "0";
$one = "1";
// Update the database and set notify to current date/time
$sql = "UPDATE once SET notify=:zero WHERE username1=:logusername UNION 
        UPDATE daily SET notify=:zero WHERE username1=:logusername UNION 
        UPDATE weekly SET notify=:zero WHERE username1=:logusername UNION 
        UPDATE monthly SET notify=:zero WHERE username1=:logusername UNION
        UPDATE yearly SET notify=:zero WHERE username1=:logusername UNION
        UPDATE dailyround SET notify=:zero WHERE username1=:logusername UNION
        UPDATE dailydaytime SET notify=:zero WHERE username1=:logusername UNION 
        UPDATE dailynight SET notify=:zero WHERE username1=:logusername UNION 
        UPDATE dailyweekdaytime SET notify=:zero WHERE username1=:logusername UNION
        UPDATE dailyweekdaynight SET notify=:zero WHERE username1=:logusername UNION
        UPDATE dailyweekendday SET notify=:zero WHERE username1=:logusername UNION
        UPDATE dailyweekendnight SET notify=:zero WHERE username1=:logusername";
$stmt = $db_connect->prepare($sql);
$stmt->bindParam(':logusername', $log_username, PDO::PARAM_STR);
$stmt->bindParam(':zero', $zero, PDO::PARAM_STR);
$stmt->execute();

Open in new window

How can I use UPDATE with UNION? Or is there another way of doing this?

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adebayo Ojo

ASKER

So how would I implement $sql and $stmt on the above? Can you help with the complete solution - bindParam(), execute() and all that....

Pawan Kumar

Create this SP..

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN logusername VARCHAR(255))
 BEGIN
		UPDATE once SET notify=:zero WHERE username1=logusername  
		UPDATE daily SET notify=:zero WHERE username1=logusername  
		UPDATE weekly SET notify=:zero WHERE username1=logusername  
		UPDATE monthly SET notify=:zero WHERE username1=logusername 
		UPDATE yearly SET notify=:zero WHERE username1=logusername 
		UPDATE dailyround SET notify=:zero WHERE username1=logusername 
		UPDATE dailydaytime SET notify=:zero WHERE username1=logusername  
		UPDATE dailynight SET notify=:zero WHERE username1=logusername  
		UPDATE dailyweekdaytime SET notify=:zero WHERE username1=logusername 
		UPDATE dailyweekdaynight SET notify=:zero WHERE username1=logusername 
		UPDATE dailyweekendday SET notify=:zero WHERE username1=logusername 
		UPDATE dailyweekendnight SET notify=:zero WHERE username1=logusername
 END //
DELIMITER ;

Open in new window

call this SP with parameter - logusername. Once you call this sp all the tables will be updated at one go itself.

Adebayo Ojo

ASKER

Sorry, you are confusing me the more.......where did you come about GetOfficeByCountry?

Pawan Kumar

This is the name of the stored procedure...You can give any Name like UpdateTablesSP.. or name of your choice.

DELIMITER //
CREATE PROCEDURE UpdateTablesSP(IN logusername VARCHAR(255))
 BEGIN
		UPDATE once SET notify=:zero WHERE username1=logusername  
		UPDATE daily SET notify=:zero WHERE username1=logusername  
		UPDATE weekly SET notify=:zero WHERE username1=logusername  
		UPDATE monthly SET notify=:zero WHERE username1=logusername 
		UPDATE yearly SET notify=:zero WHERE username1=logusername 
		UPDATE dailyround SET notify=:zero WHERE username1=logusername 
		UPDATE dailydaytime SET notify=:zero WHERE username1=logusername  
		UPDATE dailynight SET notify=:zero WHERE username1=logusername  
		UPDATE dailyweekdaytime SET notify=:zero WHERE username1=logusername 
		UPDATE dailyweekdaynight SET notify=:zero WHERE username1=logusername 
		UPDATE dailyweekendday SET notify=:zero WHERE username1=logusername 
		UPDATE dailyweekendnight SET notify=:zero WHERE username1=logusername
 END //
DELIMITER ;

Open in new window

Adebayo Ojo

ASKER

Did you deliberately remove the colon between 'username1=logusername? My original statement was username1=:logusername.
Also what will now happen to the remaining part of the script:

$stmt = $db_connect->prepare($sql);
$stmt->bindParam(':logusername', $log_username, PDO::PARAM_STR);
$stmt->bindParam(':zero', $zero, PDO::PARAM_STR);
$stmt->execute();

Open in new window

Pawan Kumar

Did you deliberately remove the colon between 'username1=logusername?
Yes in SP it is not required.

Adebayo Ojo

ASKER

Okay.
Do I still need the following too?

$stmt = $db_connect->prepare($sql);
$stmt->bindParam(':logusername', $log_username, PDO::PARAM_STR);
$stmt->bindParam(':zero', $zero, PDO::PARAM_STR);
$stmt->execute();

Open in new window

Pawan Kumar

Yes.