php mysql update multiple table with same field?

Question

I have 5 table in a single database and same time of field name. the things i need to is bellow

UPDATE `customers` SET  `username`='new' WHERE `username`='old';
UPDATE `radacct` SET `username`='new' WHERE `username`='old';
UPDATE `radcheck` SET `username`='new' WHERE `username`='old';
UPDATE `radreply` SET `username`='new' WHERE `username`='old';
UPDATE `radusergroup` SET  `username`='new' WHERE `username`='old';

now how can i update all table in a single query? I have tried with

UPDATE `customers`,`radacct`,`radcheck`,`radreply`,`radusergroup` SET  `username`='new' WHERE `username`='old'

but its giving me error

1052 - Column 'username' in field list is ambiguous

looking for solution

Just because those column identifiers have the same name doesn't mean you can refer to all of them at once. You still need to specify each column individually in your query: — John Conde
– John Conde, Commented Aug 7, 2016 at 17:46
try a multi query and does that column actually exist and in all tables? what you tried won't work btw. you need a multi query. — Funk Forty Niner
– Funk Forty Niner, Commented Aug 7, 2016 at 17:46

vipin sharma · Accepted Answer · 2016-08-07 18:08:23Z

1

You can do this with help of join.

  UPDATE customers, 
           radacct, 
           radcheck,
           radreply,
           radusergroup  
    SET    customers.username = "new", 
           radacct.username = "new", 
           radcheck.username = "new"
           radreply.username = "new"    
           radusergroup.username = "new"
   WHERE   customers.username = "old"
            AND radacct.username = "old"
            AND radcheck.username = "old" 
            AND radreply.username = "old"  
            AND radusergroup.username = "old"

answered Aug 7, 2016 at 18:08

vipin sharma

1561 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kameneth · Accepted Answer · 2016-08-07 18:10:38Z

0

If what you want is one call to query on php side, i would do:

$mysqli->query("UPDATE `customers` SET  `username`='new' WHERE `username`='old'; UPDATE `radacct` SET `username`='new' WHERE `username`='old'; UPDATE `radcheck` SET `username`='new' WHERE `username`='old'; UPDATE `radreply` SET `username`='new' WHERE `username`='old'; UPDATE `radusergroup` SET  `username`='new' WHERE `username`='old';");

If you want one query on mysql side, well since those table updates are not dependant from each other, i think there is no point as it makes a heavy query (as you can see on other answer).

answered Aug 7, 2016 at 18:10

Kameneth

1125 bronze badges

Collectives™ on Stack Overflow

php mysql update multiple table with same field?

1052 - Column 'username' in field list is ambiguous

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1052 - Column 'username' in field list is ambiguous

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related