用两条SQL语句: insert into b (mac) select a.mac from a left join b on a.mac=b.mac where b.mac is nullupdate b inner join b on a.a.mac=b.mac set ....
这两句全部写在事件中吗,我不太会sql语言,看这两句联合有点看不懂
这是我写的插入 create event myevent_E on schedule every 1 day starts current_timestamp do insert into dvmanager_pc_info (v_mainboardModel,v_diskSize,v_memoryModel, v_memorySize,v_cpu,v_os,v_mac,v_hostname ) select v_mainboard,v_diskSize,v_memoryModel,v_memorySize, v_cpuModel,v_OS,v_hostMac,v_hostName from manager where v_hostMac not in(select v_mac from dvmanager_pc_info);update dvmanager_pc_info inner join dvmanager_pc_info on manager.v_hostMac=dvmanager_pc_info.v_mac set..这个更新语句能帮我写详细一点吗,我不太懂
你的表dvmanager_pc_info到底有没有主键? 主键是什么?如果MAC地址是主键,则你可以直接使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 语句问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
dvmanager_pc_info的主键我还没有设置,就是因为对这个想法模棱两可,想要通过操作使新的数据插入,旧的数据更新,可是自己在数据库方面根本就驾驭不了我的想法,所以能详细一点吗?就以这句分析 insert into dvmanager_pc_info (v_mainboardModel,v_diskSize,v_memoryModel, v_memorySize,v_cpu,v_os,v_mac,v_hostname ) select v_mainboard,v_diskSize,v_memoryModel,v_memorySize, v_cpuModel,v_OS,v_hostMac,v_hostName from manager where v_hostMac not in(select v_mac from dvmanager_pc_info); 如果此表以mac地址为主码完整的代码该如何实现
insert into dvmanager_pc_info (v_mainboardModel,v_diskSize,v_memoryModel,v_memorySize,v_cpu,v_os,v_mac,v_hostname) select v_mainboard,v_diskSize,v_memoryModel,v_memorySize,v_cpuModel,v_OS,v_hostMac,v_hostName from manager ON DUPLICATE KEY UPDATE v_mainboardModel=VALUES(v_mainboardModel), v_diskSize=VALUES(v_diskSize), v_memoryModel=VALUES(v_memoryModel), v_memorySize=VALUES(v_memorySize), v_os=VALUES(v_os), v_hostname=VALUES(v_hostname);
insert into b (mac)
select a.mac from a left join b on a.mac=b.mac where b.mac is null
insert into b (mac)
select a.mac from a left join b on a.mac=b.mac where b.mac is nullupdate b inner join b on a.a.mac=b.mac set ....
create event myevent_E on schedule every 1 day starts current_timestamp
do
insert into dvmanager_pc_info
(v_mainboardModel,v_diskSize,v_memoryModel,
v_memorySize,v_cpu,v_os,v_mac,v_hostname
)
select v_mainboard,v_diskSize,v_memoryModel,v_memorySize,
v_cpuModel,v_OS,v_hostMac,v_hostName from manager where v_hostMac not in(select v_mac from dvmanager_pc_info);update dvmanager_pc_info inner join dvmanager_pc_info on manager.v_hostMac=dvmanager_pc_info.v_mac set..这个更新语句能帮我写详细一点吗,我不太懂
insert into dvmanager_pc_info
(v_mainboardModel,v_diskSize,v_memoryModel,
v_memorySize,v_cpu,v_os,v_mac,v_hostname
)
select v_mainboard,v_diskSize,v_memoryModel,v_memorySize,
v_cpuModel,v_OS,v_hostMac,v_hostName from manager where v_hostMac not in(select v_mac from dvmanager_pc_info);
如果此表以mac地址为主码完整的代码该如何实现
select v_mainboard,v_diskSize,v_memoryModel,v_memorySize,v_cpuModel,v_OS,v_hostMac,v_hostName
from manager
ON DUPLICATE KEY UPDATE
v_mainboardModel=VALUES(v_mainboardModel),
v_diskSize=VALUES(v_diskSize),
v_memoryModel=VALUES(v_memoryModel),
v_memorySize=VALUES(v_memorySize),
v_os=VALUES(v_os),
v_hostname=VALUES(v_hostname);