troubleshooting Question

Need help with SQL statement using update and inner join of same table

Avatar of rleyba828
rleyba828Flag for Australia asked on
PHPMySQL ServerSQL
3 Comments2 Solutions790 ViewsLast Modified:
Hi Team,

   I am using mysql (mysql  Ver 14.12 Distrib 5.0.77,)  on centos 5.5.   Just need some syntax help with an update statement I need where the info it needs to update the table is within the same table itself.  I have a table of MAC addresses and you can have different MAC addresses in the same SwitchPort.  I need to populate a column called "MACS_LEARNED_ON_PORT" to total how many mac addresses are found on the same Device on the same port.  See scenario below.

DEVICENAME       SwitchPORT      MAC                     MACS_LEARNED_ON_PORT
Router-A              G0/1             aa:bb:cc:dd:01             3       
Router-A              G0/2             bb:cc:dd:ee:ff              2
Router-A              G0/3             bb:cc:dd:ee:ff              1
Router-A              G0/4             bb:cc:dd:ee:ff              1
Router-A              G0/1             bb:cc:dd:ee:02            3
Router-A              G0/2             bb:cc:dd:ee:ff             2
Router-A              G0/1             bb:cc:dd:ee:11           3

I was google searching and seems that to use the same table, you need to ALIAS it with another name.  However, the two methods below are failing.  What might be the correct syntax.
UPDATE MAC_SEARCH as A  INNER JOIN MAC_SEARCH as B on (A.DEVICENAME=B.DEVICENAME and A.SwitchPORT=B.SwitchPORT)
SET A.MACS_LEARNED_ON_PORT= (SELECT COUNT(MAC) from A)

Another method I tried which is also failing is
Update  A SET MACS_LEARNED_ON_PORT=SELECT COUNT(MAC) FROM  MAC_SEARCH A
INNER JOIN MAC_SEARCH  B   on (A.DEVICENAME=B.DEVICENAME and A.SwitchPORT=B.SwitchPORT)

THanks very much.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros