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?
Adebayo OjoRegional Network ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Single update statement will only update single table. We cannot use UNION and update multiple tables.

We need to use like below -

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Adebayo OjoRegional Network ManagerAuthor Commented:
So how would I implement $sql and $stmt on the above? Can you help with the complete solution - bindParam(), execute() and all that....
Pawan KumarDatabase ExpertCommented:
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.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Adebayo OjoRegional Network ManagerAuthor Commented:
Sorry, you are confusing me the more.......where did you come about GetOfficeByCountry?
Pawan KumarDatabase ExpertCommented:
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 OjoRegional Network ManagerAuthor Commented:
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 KumarDatabase ExpertCommented:
Did you deliberately remove the colon between 'username1=logusername?
Yes in SP it is not required.
Adebayo OjoRegional Network ManagerAuthor Commented:
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 KumarDatabase ExpertCommented:
Yes.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.