SQL Statement to copy value from field1 to field2 then clear data in field 2 2

[davismar]

Thanks to whomever might have this SQL statement for me to try!

The table name is ACCOUNTS.

Field 1 = ARCustomerNumber
Field 2 = AccountID

We would like a SQL statement that will
1. Clear the data from Field 2 ACCOUNTID
2. Copy the data from Field 1 ARCUSTOMERNUMBER into Field 2 ACCOUNTID
3. Clear the data from Field 1 ARCUSTOMERNUMBER and make it blank.

Thanks again for the help!

 

[TamarGranor]

Try this:

UPDATE ACCOUNTS
        SET AccountID = ARCustomerNumber,
            ARCustomerNumber = ' '

Be aware that, as written, this will affect every record in the table. If that's not what you mean to do, you need to add a WHERE clause to limit the records affected.

Tamar

 

[davismar]

Thank you, Thank you so much!

That worked perfectly!
If I did want to add a WHERE clause, what would that look like?
For example, where ACCOUNTTYPE = CSA Customer

Another question:

Would you know how I can then do the following:

Copy the data from the ACCOUNTNAME to the ARCustomerNumber field?

Again - I very much appreciate the help!

 

[SQLBill]

Using Tamar's code, here' how you would add the WHERE clause:

UPDATE ACCOUNTS
        SET AccountID = ARCustomerNumber,
            ARCustomerNumber = ' ' 
WHERE  ACCOUNTTYPE = 'CSA Customer'

Now for: Copy the data from the ACCOUNTNAME to the ARCustomerNumber field?
You don't give us examples of ACCOUNTNAME, but you can use Tamar's code for the same thing. Instead of updating AccountID with ARCustomerNumber, you would update ARCustomerNumber with AccountName. If you want to do it all in one step:

UPDATE ACCOUNTS
        SET AccountID = ARCustomerNumber,
            ARCustomerNumber = AccountNumber
WHERE  ACCOUNTTYPE = 'CSA Customer'

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[davismar]

Thank you SQL Bill!

Very much appreciated!