SQL UPDATE (but only if its going to be unique)

I need to write an SQL string that will UPDATE a value into a table, but only if that value is unique. But it should allow blank values ('') to be duplicates.

UPDATE [knw].[dbo].[Nop_ProductVariant] 

SET barcode = (CASE WHEN (SELECT count(*) FROM [knw].[dbo].[Nop_ProductVariant] WHERE barcode = '" + item + "') = 0 THEN '" + item + "' ELSE开发者_StackOverflow '' END)

WHERE ProductVariantId='" + s + "'"

This functions seems to wipe all data and make them blank (''). I think this is because if the current value that I am changing is the same value, (i.e. changing 'purple' to 'purple') it thinks it would be a duplicate and puts a blank one in.

Help!

Could you modify the SELECT inside your CASE slightly to exclude the current ProductVariantId?

SELECT count(*) 
FROM [knw].[dbo].[Nop_ProductVariant] 
WHERE barcode = '" + item + "'
AND ProductVariantId <> '" + s + "'"

I guess you want something like this:

UPDATE [knw].[dbo].[Nop_ProductVariant]
SET barcode = @NewValue
WHERE NOT EXISTS
  (SELECT 1
   FROM [knw].[dbo].[Nop_ProductVariant]
   WHERE barcode = @NewValue)
AND ... ? ;

Why not simply run it in two queries:

Query 1:
Update [knw].[dbo].[Nop_ProductVariant] 
Set barcode = '"' + [knw].[dbo].[Nop_ProductVariant].item + '"'
Where ProductVariantId='"' + s + '"'
    And Exists  (
                Select 1
                From [knw].[dbo].[Nop_ProductVariant] As PV1
                Where PV1.barcode = '"' + [knw].[dbo].[Nop_ProductVariant].[item] + '"'
                )

Query 2:
Update [knw].[dbo].[Nop_ProductVariant] 
Set barcode = ''
Where ProductVariantId='"' + s + '"'
    And Not Exists  (
                    Select 1
                    From [knw].[dbo].[Nop_ProductVariant] As PV1
                    Where PV1.barcode = '"' + [knw].[dbo].[Nop_ProductVariant].[item] + '"'
                    )

Normally, you should use a UNIQUE index for such situations.

In SQL Server 2008:

CREATE UNIQUE INDEX ux_productvariant_barcode ON (barcode) WHERE barcode <> ''

If you are not able to create such an index, use this:

WITH    q AS
        (
        SELECT  *
        FROM    [knw].[dbo].[Nop_ProductVariant]
        WHERE   ProductVariantId = @s
        )
UPDATE  q
SET     barcode = @item
WHERE   @item NOT IN
        (
        SELECT  barcode
        FROM    q
        WHERE   barcode <> ''
        )

This shows all unique non-empty barcodes along with their first-entry IDs:

SELECT barcode, MIN(ProductVariantId) AS ProductVariantId
FROM [knw].[dbo].[Nop_ProductVariant]
WHERE barcode <> ''
GROUP BY barcode

Accordingly, this shows all their duplicates (full rows this time, not including the IDs from the previous selection):

SELECT npv.*
FROM [knw].[dbo].[Nop_ProductVariant] AS npv
  LEFT JOIN (
    SELECT MIN(ProductVariantId) AS ProductVariantId
    FROM [knw].[dbo].[Nop_ProductVariant]
    WHERE barcode <> ''
    GROUP BY barcode
  ) npv_u ON npv.ProductVariantId = npv_u.ProductVariantId
WHERE npv.barcode <> '' AND npv_u.ProductVariantId IS NULL

Personally I would first check out those lists. If they are alright, the following script can be used to blank the duplicates:

UPDATE npv
SET barcode = ''
FROM [knw].[dbo].[Nop_ProductVariant] AS npv
  LEFT JOIN (
    SELECT MIN(ProductVariantId) AS ProductVariantId
    FROM [knw].[dbo].[Nop_ProductVariant]
    WHERE barcode <> ''
    GROUP BY barcode
  ) npv_u ON npv.ProductVariantId = npv_u.ProductVariantId
WHERE npv.barcode <> '' AND npv_u.ProductVariantId IS NULL