Hi,
> ERROR: column reference "firewall" is ambiguous > LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... set firewal = misses a table HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl 2013/12/4 Eric Lamer <e...@phoenixsecure.com> > Hi, > > > > I need some help with upsert. > > > > Some info on what I want to do: > > > > Each day I create a table for my firewall logs. I have one entry for > one occurrence of all the field except sum, which is the number of > occurrence I have of each log that match all the field. My table has the > following field: > firewall,action,src_zone,src_ip,dst_zone,dst_ip,proto,port,hex1,hex2,sum > > > > Each day I want to copy the last 7 days into one table so I have one > table with the last 7 days of logs. > > > > So I want to copy the data from 7 tables into 1. If the row does not > exist I just insert and if the row already exist I just update the sum > (existing sum + new sum). > > > > Public.test is the table I use for the last 7 days logs. > > daily.daily_20131202 is table for 1 day. > > I will run this command 7 times with different daily table. > > > > WITH upsert as > > (update public.test T set > firewall=S.firewall,action=S.action,src_zone=S.src_zone,src_ip=S.src_ip,dst_zone=S.dst_zone,dst_ip=S.dst_ip,proto=S.proto,port=S.port,hex1=S.hex1,hex2=S.hex2,sum=T.sum+S.sum > from daily.daily_20131202 S where (T.firewall=S.firewall and > T.action=S.action and T.src_zone=S.src_zone and T.src_ip=S.src_ip and > T.dst_zone=S.dst_zone and T.dst_ip=S.dst_ip and T.proto=S.proto and > T.port=S.port and T.hex1=S.hex1 and T.hex2=S.hex2) RETURNING * ) > > insert into public.test select * from daily.daily_20131202 a WHERE NOT > EXISTS (SELECT * FROM upsert b WHERE a.firewall=b.firewall and > a.action=b.action and a.src_zone=b.src_zone and a.src_ip=b.src_ip and > a.dst_zone=b.dst_zone and a.dst_ip=b.dst_ip and a.proto=b.proto and > a.port=b.port and a.hex1=b.hex1 and a.hex2=b.hex2); > > > > When I run the command I get an error > > ERROR: column reference "firewall" is ambiguous > > LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... > > > > Any idea what I am doing wrong? > > > > Also, is there an easier way to do that? > > > > Thanks for the help. >
