How to update only exact value matches?

I am using SQL update query [in powershell script] to update machine name present in column.

The column values are stored as URL. So machine name is part of the string.

  • update table on click of a button
  • Select Item in DataGridViewComboBoxColumn loaded from SQL
  • MS Access SQL Server DB - Query Syntax for CAST Function
  • select case with “over partition by”
  • Is routing messages to the msdb SQL database required in SQL Service Broker?
  • Trigger for not updating and delete data
  • ( Column Name : URL
    Example column value : “http://Machine1:80/Impl100/Core/Data/Config.0”)

    I am using following query to do the update.

    $UpdateQuery="UPDATE [$DatabaseName].[dbo].[$TableName] 
    SET $columnName=Replace("$ColumnName", '$OldMachineName', '$NewMachineName');"

    Assume my old machine name is “Machine1” and i am to replace with “AppMachine1”. If i execute the script ,it will update “AppMachine1”. But if i execute the script second time It will update it as “AppAppMachine1” as Machine1 is part of AppMachine1.

    Is it possible to get the column value and check the machine name before update?

    How to do this?

    2 Solutions collect form web for “How to update only exact value matches?”

    Try this

    $UpdateQuery="UPDATE [$DatabaseName].[dbo].[$TableName] 
    SET $columnName=Replace("$ColumnName", '$OldMachineName', '$NewMachineName')
    WHERE "$ColumnName" <> '$NewMachineName';"

    Include the double leading forward slashes:

     ... -replace '//machine1','//AppAppMachine1'

    You also need to put quoted variables in double quotes. Single quotes disables variable expansion and as a result variables will no be replaced with its values.

    Here’s an example of a double replace:

    PS> $url = 'http://machine1:80/Impl100/Core/Data/Config.0'
    PS> $url -replace '//machine1','//AppAppMachine1' -replace '//machine1','//AppAppMachine1'
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.