-2

How can I add two columns (amount, amount2) if they have the same value in multiple columns (Account,Account2) using UPDATE statement. I want to keep all rows even if Account <> Account 2. Also i want them in two columsn only Account and Amount. I am using MS Access sql view. * I want to update the table not just use a select statement*

For example: I want to add Amount and Amount2 columns only if Account, Account 2 columns match.

I want that result end to be only two columns, the Account and the amount that was added together.

Account Amount Account2 Amount2
1234576  4      1234576    4
4444444  10     4564888    11
456789   2      456789      2
000000   1      1111111     6
Ben21
  • 93
  • 9
  • 2
    Have you tried any code? You have something written that is not working? – Wookies-Will-Code Aug 13 '18 at 14:39
  • Yes the code below works and adds both columns when account and account2 are equal but it still leaves columns (account2, amount2) there. I cannot use a DROP on them because there is some on those columns that are <> (not equal). UPDATE allocation SET amount = amount + amount2 WHERE account=account2; – Ben21 Aug 13 '18 at 14:41
  • Eh.. I've shared an answer, but now I'm unsure what you want. You can't have a column present for half your table, and removed for the other half. You can set them to `Null`, however. – Erik A Aug 13 '18 at 14:42
  • You would have to do a self join (left) on the Account = Account2 and take the sum of Amount and Amount2. Take care of the NULLs.Then when you have a nice result, you can refer to https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server to convert it to UPDATE statement. – Minus Aug 13 '18 at 14:51

1 Answers1

0

This is non-normalized table design, and I recommend you adjust your design instead, but this can easily be achieved:

UPDATE MyTable
SET [Amount] = [Amount] + [Amount2],
[Amount2] = Null,
[Account2] = Null
WHERE [Account] = [Account2]

Then if you want to move the account and amount to a single column after:

INSERT INTO MyTable(Account, Amount)
SELECT Account2, Amount2
FROM MyTable
WHERE Account2 IS NOT NULL Or Amount2 IS NOT NULL

Then, remove the unnecessary columns

ALTER MyTable DROP COLUMN Account2, Amount2
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • thank you Erik. This added both columns but left the <> ones on columns account2, and amount 2. Is there anyways to put them under column account, and amount ? I need to compare data with another table and have them the same format. Only two columns (account and amount). – Ben21 Aug 13 '18 at 15:43