CODEWITHSUNDEEP
sqlsql-server
Z34587
Z34587

Reputation: 33

Update a row when a different row doesn't exist

The code shown doesn't work, but shows what I'm trying to do. I want to change the ID of a row that exists to an ID that doesn't exist.

(
    SELECT
    [ClientID],
    [TypeID],
    [Text]
    FROM [ClientTable]
    WHERE
        [TypeID] = 18
    AND [Text] <> ''
) toMove

IF NOT EXISTS
(
    SELECT *
    FROM [ClientTable]
    WHERE
        [ClientID] = toMove.[ClientID]
    AND [TypeID] = 17
)
BEGIN
    UPDATE [ClientTable]
    SET [TypeID] = 17
    WHERE
        [ClientID] = toMove.[ClientID]
    AND [TypeID] = toMove.[TypeID]
END

I'd like any row with TypeID = 18 to change to TypeID = 17 if no row like that already exists for each ClientID. I'm expecting there to be many altered rows after executing. Using SQL Server

Edit: Sample data and expected results

Original Data
ClientID    TypeID  Text
123         18      some text
123         17  
456         18      more text
789         18  

Expected Output
ClientID    TypeID  Text
123         18      some text
123         17  
456         17      more text
789         18

Upvotes: 0

Views: 319

Answers (3)

JIKEN
JIKEN

Reputation: 337

First get count based on below select script and after that apply below update script. Both record count should be match. (e.g. 10 count in select script same in update statement)

SELECT COUNT(1) TotalRecordsFound
FROM [ClientTable] AS C1
WHERE C1.[TypeID] = 18
AND C1.[Text] <> ''
AND NOT EXISTS
(
SELECT 1 FROM ClientTable AS C2
WHERE C2.ClientID = C1.ClientID
AND C2.TypeID = 17
)

You are trying to update TypeID as 17 whose TypeID as 18 and Text column is not empty with where TypeID not 17 for that cleintId. That is simply work with below update script.

UPDATE C1
SET C1.[TypeID] = 17
-- You can add below two columns while updating records
--,UpdatedBy = 100
--,UpdatedDateTime = GETDATE()
FROM [ClientTable] AS C1
WHERE C1.[TypeID] = 18
AND C1.[Text] <> ''
AND NOT EXISTS
(
SELECT 1 FROM ClientTable AS C2
WHERE C2.ClientID = C1.ClientID
AND C2.TypeID = 17
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this in various ways. One method uses window functions:

WITH toupdate AS (
      SELECT ct.*,
             SUM(CASE WHEN TypeId = 17 THEN 1 ELSE 0 END) OVER (PARTITION BY ClientId) as num_17s             
      FROM ClientTable t
      WHERE Text <> ''
     )
UPDATE toupdate
    SET TypeId = 17
    WHERE TypeId = 18 AND Text <> '' AND num_17s = 0;

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65105

You can combine UPDATE statement with a SELECT combining by EXISTS :

UPDATE [ClientTable] c
   SET [TypeID] = 17
 WHERE EXISTS 
       (
        SELECT [ClientID]
          FROM [ClientTable]
         WHERE [TypeID] = 18
           AND [Text] <> ''
           AND [ClientID] = c.[ClientID]
       )

Upvotes: 0

Related Questions