Update rows using an IF statement

Question

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

You need to store that account number into a variable to be used later or use `IF(EXISTS(SELECT....)) BEGIN...END` — S3S, Dec 09 '16 at 20:00
the error message is very clear. you don't have a CMRR alias defined in the `update` statement. — Vamsi Prabhala, Dec 09 '16 at 20:00
@vkp `CRA_METRO2_REJECTED_RECORDS AS CMRR` and this error message happens before the `UPDATE` — Jared, Dec 09 '16 at 20:03
i see..you would get an error in `update` too later on. `IF CMRR.account_number = ''` is causing the error. after the `select` finishes the alias `CMRR` is invalid. you can store the account number in a variable and use it for `IF`. — Vamsi Prabhala, Dec 09 '16 at 20:05
@vkp that is not the only line that is giving me errors. each time I reference `CMRR` after the `IF` is causing issues. I might have to do something like @scsimon mentioned — Jared, Dec 09 '16 at 20:07
This is not how you construct your update statements. Try reading through this for examples on how to construct - http://stackoverflow.com/questions/982919/sql-update-query-using-joins — Anand, Dec 09 '16 at 20:10
I'm totally confused. Do you want to run a SELECT query or do you want to do UPDATE? You cannot do both. — DVT, Dec 09 '16 at 20:14
I want to run an Update, based on empty account numbers for that batch. — Jared, Dec 09 '16 at 20:41

SQLChao · Accepted Answer · 2016-12-09T21:11:57.950

2

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 = ''

edited Dec 09 '16 at 21:11

answered Dec 09 '16 at 20:14

SQLChao

7,709
1
17
32

So the problem now is that this doesn't update all of my rows which account number is empty. – Jared Dec 09 '16 at 20:33
@Jared You are filtering on id. So whatever id is returned by the initial select are the only ones that will be updated. – SQLChao Dec 09 '16 at 20:38
Yes, by `batch_id` however there are 66,000 some records and some of those have empty account numbers. Or can you seem something wrong with the way I'm filtering? – Jared Dec 09 '16 at 20:40
@jared Can you come to this chat we don't have to chat over comments. http://chat.stackoverflow.com/rooms/130254/for-jared – SQLChao Dec 09 '16 at 20:42

Update rows using an IF statement

1 Answers1