CODEWITHSUNDEEP
sql-servert-sql
Jared
Jared

Reputation: 416

Update rows using an IF statement

I'm having difficulties updating a result set depending on some condition. Some of the account numbers come in empty but match based on a different identifier.

I'm updating the empty account numbers with the matching one, but can't quite get the syntax to work. I'm running into the error

The multi-part identifier "CMRR.aacount_number' could not be found

This is at a few different places I'm referencing the other table.

DECLARE @BatchId INT
SET @BatchId = 1030

SELECT 
    CMRR.id,
    CMRR.account_number,
    CMRR.URN
FROM 
    CRA_METRO2_REJECTED_RECORDS AS CMRR
WHERE 
    batch_id = @BatchId

IF CMRR.account_number = ''
BEGIN
    UPDATE CRA_METRO2_REJECTED_RECORDS 
    SET CMRR.account_number = (SELECT account_number 
                               FROM CRA_METRO2_REJECTED_RECORDS
                               WHERE URN = CMRR.URN
                                  AND account_number != '')
    WHERE id = CMRR.id
END

Upvotes: 0

Views: 89

Answers (1)

SQLChao
SQLChao

Reputation: 7837

I believe this does what you are wanting. I added 3 variables to hold the values from your select statement. You'll want to check the datatypes as I just used INT not knowing what they are in your system. Then use those variables in your update statement.

DECLARE @BatchId INT

-- Added variables 
DECLARE @id INT
DECLARE @urn INT
DECLARE @account_number INT

SET @BatchId = 1030

-- Assign values to variables
SELECT 
    @id = id
  , @account_number = account_number
  , @urn = urn
FROM CRA_METRO2_REJECTED_RECORDS
WHERE batch_id = @BatchId


IF @account_number = ''
BEGIN
  UPDATE CRA_METRO2_REJECTED_RECORDS 
  SET account_number = (SELECT account_number 
                             FROM CRA_METRO2_REJECTED_RECORDS
                             WHERE URN = @URN
                             AND account_number != '')
  WHERE id = @id
END

EDIT

After a brief chat this was the solution.

UPDATE cmrr1
SET cmrr1.account_number = cmrr2.account_number
FROM CRA_METRO2_REJECTED_RECORDS cmrr1
JOIN CRA_METRO2_REJECTED_RECORDS  cmrr2 ON cmrr1.URN = cmrr2.URN AND cmrr2.account_number <> ''
WHERE cmrr1.batchid = @batchid
AND cmrr1.accound_number = ''

Upvotes: 2

Related Questions