UPDATE with FROM -- can't figure out where I'm going wrong

  • Hi fellow SQL Server gurus,

    *Edit 1: Corrected update statement WHERE clause to match the SELECT. Still same result, however.

    We have the following table structure:

    DBA_P_Instancenames (contains SQL Server instance names)

    DBA_P_PhysicalServers (lists physical and VM's that we host -- table was around before VM's were introduced to the environment)

    DBA_P_VirtualServers (contains SQL Server Network Names for clustered instances).

    DBA_P_ClusterNodes (contains windows cluster names and computer names from the DBA_P_PhysicalServers table)

    For better or worse, the instance names table has a computername column that *should* match up to the computername column on the DBA_P_VirtualServers table, and when the instance is found to be running on a different node, we log a ticket/email citing that the instance of SQL is not running on the proper node.

    We don't have a front-end managing this data, and sometimes the computername column gets updated on the dba_p_instancenames table, but not on the DBA_P_VirtualServers tables. This is where I am struggling to come up with a proper-set based update statement to update the values accordingly.

    For this, we can assume that the computer names exist in the cluster nodes table and the physical servers table.

    Instance Names table:

    InstanceVirtualName2\NamedInstance2 | ComputerName1 (properly updated after a failover to ComputerName1)

    Virtual Servers table:

    InstanceVirtualName2 | ComputerName2 (was not updated after a failover to ComputerName1, only contains the network name and the virtual IP on this table, not the instance name)

    The code below is what I have come up with so far, but cannot get it functioning properly.

    I know I can change it to a cursor-based RBAR (as Jeff Moden puts it) operation, but the mental block on getting this functioning is frustrating me.

    --this code returns one row for testing, although I have upwards of

    --select a list of the instances that need to be updated

    select network_name = (case

    when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename

    when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)

    end),

    dpp.computerName,

    dpv.computername,

    dpi.instancename

    from DBA_P_instancenames dpi

    inner join DBA_P_PhysicalServers dpp on dpi.computerName = dpp.computername --these should match

    left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computername --left outer so dpi will be pulled even if it doesn't find a match on dpv

    WHERE dpp.computername IN (SELECT computer_name from dbo.DBA_P_ClusterNodes)

    and dpv.computername is null -- means names do not match between dpi and dpv

    --single out a specific instance

    and (case

    when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename

    when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)

    end) = 'InstanceVirtualName2'

    --this is returning 0 rows when updating, when there is one record that should be updated.

    UPDATE [dbo].[DBA_P_VirtualServers]

    SET [computerName] = dpp.computerName

    from DBA_P_instancenames dpi

    inner join DBA_P_PhysicalServers dpp on dpi.computerName = dpp.computername --these should match

    left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computername --left outer so dpi will be pulled even if it doesn't find a match on dpv

    WHERE dpp.computername IN (SELECT computer_name from dbo.DBA_P_ClusterNodes) -- computername from physical servers is part of a cluster

    and dpv.computername is null -- means names do not match between dpi and dpv

    and (case

    when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename

    when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)

    end) = 'InstanceVirtualName2'

    Thank you very much in advance for helping out.

    Cheers,

    Steve

  • Are you sure this is what you want in your update?

    SELECT SUBSTRING('SERVERNAME\NAMEDINSTANCE', 1, CHARINDEX('\', 'SERVERNAME\NAMEDINSTANCE', 1) - 1)

    Because this gives the server name in the case of a named instance (assuming your data is SERVERNAME\NAMEDINSTANCE), but the instance name in the other case. Is this what you want?

    Jared
    CE - Microsoft

  • Look at the difference between this:

    and (case

    when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename

    when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)

    end) = 'InstanceVirtualName2'

    and this:

    and dpv.machineName = (case

    when CHARINDEX('\', dpi.instancename, 1) = 0 then dpi.instancename

    when CHARINDEX('\', dpi.instancename, 1) > 0 then SUBSTRING(dpi.instancename, 1, CHARINDEX('\', dpi.instancename, 1) - 1)

    end)

    and dpv.machineName = 'InstanceVirtualName2'

    One has 1 condition and the other is 2.

    Jared
    CE - Microsoft

  • Whoops- Let me edit that.. Sorry for the confusion.

    To your other question, yes, we are looking to join just the Server Name (Network Name) for the clustered instance.

    The virtual servers table holds

    machineName (aka NetworkName) | computerName | VIP | clusterName

    So, to join the Instance Names table to the Virtual Servers table, we would then need to join the Network Name to the Server Name portion of the instance Name.

    Thanks for your response.

  • S. Kusen (2/20/2012)


    Whoops- Let me edit that.. Sorry for the confusion..

    Was that a pasting error or was that the cause of your update issue? 🙂

    Jared
    CE - Microsoft

  • SQLKnowItAll (2/20/2012)


    S. Kusen (2/20/2012)


    Whoops- Let me edit that.. Sorry for the confusion..

    Was that a pasting error or was that the cause of your update issue? 🙂

    LOL- Wasn't the cause.. I wish it was :hehe:

    I feel like I'm either missing a JOIN or something since it is not making the "connection" between dbo.DBA_P_VirtualServers and the dpv alias that I'm giving the table lower in the query. without the alias, the computername column becomes ambiguous.

    Truly confusing myself with this one....

    Steve

  • S. Kusen (2/20/2012)


    SQLKnowItAll (2/20/2012)


    S. Kusen (2/20/2012)


    Whoops- Let me edit that.. Sorry for the confusion..

    Was that a pasting error or was that the cause of your update issue? 🙂

    LOL- Wasn't the cause.. I wish it was :hehe:

    I feel like I'm either missing a JOIN or something since it is not making the "connection" between dbo.DBA_P_VirtualServers and the dpv alias that I'm giving the table lower in the query. without the alias, the computername column becomes ambiguous.

    Truly confusing myself with this one....

    Steve

    Strange... If the first statement returns 1 row, and the update is EXACTLY the same as the first in the from and where... There is not justification I can think of for the update not to work properly.

    Jared
    CE - Microsoft

  • I think this is a logic flaw. Your update is against the virtual server table, but you're left joining against that table and specifying you want to exclude those where the name matches. So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).

    So in essence you are looking to INSERT a record, not UPDATE a record.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (#4) (2/20/2012)


    So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).

    EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?

    But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?

    Jared
    CE - Microsoft

  • SQLKnowItAll (2/20/2012)


    Matt Miller (#4) (2/20/2012)


    So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).

    EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?

    But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?

    Except that your join criteria is based on the name as well. So you're not finding records where the name is missing or null - you're excluding them

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (#4) (2/20/2012)


    SQLKnowItAll (2/20/2012)


    Matt Miller (#4) (2/20/2012)


    So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).

    EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?

    But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?

    Except that your join criteria is based on the name as well. So you're not finding records where the name is missing or null - you're excluding them

    left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computername

    WHERE dpv.computername is null

    Returns all values where dpi.computername exists and dpv.computername does not. EDIT!!!:What's the issue?

    No records exist to update in dpv. Hence, the suggestion on insert. You got this OP?

    Jared
    CE - Microsoft

  • SQLKnowItAll (2/20/2012)


    Matt Miller (#4) (2/20/2012)


    SQLKnowItAll (2/20/2012)


    Matt Miller (#4) (2/20/2012)


    So - no record is returned since you can't join to a record that doesn't match the join (at least not as is).

    EDIT: Isn't that the point of a left join? To return unmatched records as well as matching?

    But it is a left join to find a null value and updating it with a value that is not null. I.e. finding where they don't match and updating them so that they do match. Right?

    Except that your join criteria is based on the name as well. So you're not finding records where the name is missing or null - you're excluding them

    left outer join DBA_P_VirtualServers dpv on dpi.computerName = dpv.computername

    WHERE dpv.computername is null

    Returns all values where dpi.computername exists and dpv.computername does not. EDIT!!!:What's the issue?

    No records exist to update in dpv. Hence, the suggestion on insert. You got this OP?

    Yes- I think I'm on the right track. Appreciate the extra sets of eyes! I knew I had to be flawed with the joins, I just couldn't see it after looking at it for hours.

    I'll post back when I get it working. Thanks again!

    Steve

  • Thanks guys! That join was where I had issues,and I really wanted to see where the computer names were different (where NULL that meant I had nothing to join on so the counterpart record was missing, and a separate task for correcting the data). For those interested, here is the code I wound up using successfully:

    UPDATE [dpv]

    SET [dpv].[computername] = dpp.computerName

    from [dbo].[DBA_P_instancenames] [dpi]

    inner join [dbo].[DBA_P_PhysicalServers] [dpp] on [dpi].[computerName] = [dpp].[computername] --these should match

    left outer join [dbo].[DBA_P_VirtualServers] [dpv] --left outer so dpi will be pulled even if it doesn't find a match on dpv

    ON [dpv].[machineName] = (case

    when CHARINDEX('\', [dpi].[instancename], 1) = 0 then [dpi].[instancename]

    when CHARINDEX('\', [dpi].[instancename], 1) > 0 then SUBSTRING([dpi].[instancename], 1, CHARINDEX('\', [dpi].[instancename], 1) - 1)

    end)

    WHERE [dpp].[computername] IN (SELECT [computer_name] from [dbo].[DBA_P_ClusterNodes])

    and [dpv].[computername] <> [dpp].[computername] --means names do not match between dpi and dpv

    Thanks to Matt and Jarod for helping steer me along! 😎

    Cheers,

    Steve

  • S. Kusen (2/20/2012)


    Thanks guys! That join was where I had issues,and I really wanted to see where the computer names were different (where NULL that meant I had nothing to join on so the counterpart record was missing, and a separate task for correcting the data). For those interested, here is the code I wound up using successfully:

    UPDATE [dpv]

    SET [dpv].[computername] = dpp.computerName

    from [dbo].[DBA_P_instancenames] [dpi]

    inner join [dbo].[DBA_P_PhysicalServers] [dpp] on [dpi].[computerName] = [dpp].[computername] --these should match

    left outer join [dbo].[DBA_P_VirtualServers] [dpv] --left outer so dpi will be pulled even if it doesn't find a match on dpv

    ON [dpv].[machineName] = (case

    when CHARINDEX('\', [dpi].[instancename], 1) = 0 then [dpi].[instancename]

    when CHARINDEX('\', [dpi].[instancename], 1) > 0 then SUBSTRING([dpi].[instancename], 1, CHARINDEX('\', [dpi].[instancename], 1) - 1)

    end)

    WHERE [dpp].[computername] IN (SELECT [computer_name] from [dbo].[DBA_P_ClusterNodes])

    and [dpv].[computername] <> [dpp].[computername] --means names do not match between dpi and dpv

    Thanks to Matt and Jarod for helping steer me along! 😎

    Cheers,

    Steve

    Glad to help, but it was really Matt who came to the rescue 🙂

    Jared
    CE - Microsoft

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply