Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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 Comments1 Solution790 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
Avatar of et_me
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers