0

I have created a shell script to connect to a DB and INSERT IPs and MACs from dhcpd.log file:

#/!bin/bash
dhcpLogFile="/var/log/dhcpd.log"
NumberOfLines=$(awk '/DHCPACK/ { print $8} ' $dhcpLogFile | awk '!x[$0]++'|awk 'BEGIN{i=0}{i++;}END{print i}')
j=1
while [ $NumberOfLines -gt 0 ]
do
ip=$(awk '/DHCPACK/ { print $8} ' $dhcpLogFile | awk '!x[$0]++' |cut -f$j -d$'\n')
mac=$(awk '/DHCPACK/ { print $10} ' $dhcpLogFile | awk '!x[$0]++' |cut -f$j -d$'\n')   
let "j +=1"
let "NumberOfLines -=1"
mysql -u root --password='pw' MatchingDB <<- _END_ 
INSERT INTO IP_MACTable (IP_Address, MAC) VALUES ('$ip','$mac');
_END_
done

my question is how to modify the insert query above to let it replace an existing record when the ip attribute is duplicated and to insert a new record normally when the ip attribute isn't duplicated ?

I have tried something like this but it keeps return an error (syntax error):

INSERT INTO IP_MACTable (IP_Address, MAC) VALUES ('$ip','$mac') 
ON DUPLICATE KEY UPDATE MAC = VALUES('$mac')

the error according to my server is near '$mac')

Error code is #1064

1 Answer 1

0

You can modify your insert to something like,

INSERT INTO IP_MACTable (IP_Address, MAC) VALUES ('$ip','$mac') 
ON DUPLICATE KEY UPDATE MAC = VALUES('$mac')
1
  • thanks but what if my primary key is (IP_Address, MAC)
    – Nidal
    Jun 5, 2014 at 13:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .