Need help from SQL Masters

Question

Hello all,

I need some help putting together a SQL statement which will query all interfaces where a node matches a custom property and then update a field.  I  have used report writer to get the SQL statement but I don't know how to make the update part work.  Below is the SQL I have so far which returns the results I'm looking for.

select top 10000 Nodes.Site_Type AS Site_Type,
Nodes.Site_Type AS Site_Type,
Nodes.Caption AS NodeName,
Nodes.PollInterval AS Poll_Interval,
Nodes.StatCollection AS Stat_Collection,
Interfaces.Caption AS Interface_Caption,
Interfaces.PollInterval AS Poll_Interval_1,
Interfaces.StatCollection AS StatCollection

FROM  Nodes

INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)

WHERE

(
(Nodes.Site_Type = 'HQ') AND
(Nodes.CommonName Like '%WAN%'
)

Thanks!

sedmo · Answer

I would use two separate queries to do this.  To update the fields in the nodes table use a query like this:

update nodes 
set nodes.pollinterval = ###, nodes.statcollection = ###
where nodes.site_type = 'HQ' and nodes.commonname like '%WAN%'

To update the interfaces table, use a query like this one:

update interfaces
set interfaces.pollinterval = ###, interfaces.statcollection = ###
where interfaces.nodeid in (select nodes.nodeid from nodes where nodes.site_type = 'HQ' and nodes.commonname like '%WAN%')

Disclaimer: use these queries at your own peril and always have a current backup of your database before making changes.

njoylif · Answer

I think it would be something like:

USE NetPerfMon

Update Nodes

SET Interface.PollInterval=xxx, Interface.StatCollection=yyy, Node.PollInterval=zzz, Node.StatCollection=aaa

FROM Nodes  INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)

WHERE

(
(Nodes.Site_Type = 'HQ') AND
(Nodes.CommonName Like '%WAN%'
)

if it gives you problems add the DB Name and Owner like:

SET NetPerfMon.dbo.Interface.PollInterval=xxx, NetPerfMon.dbo.Interface.StatCollection=yyy,etc...

stacy.patten · Answer

Interface.PollInterval, Interface.StatCollection, Node.PollInterval and Node.StatCollection.