Reputation: 741
In the UI, i am showing account_id
and account_name
.
The user is able to update the account_id as well as account_name and the account_id is unique for the user. Since I am allowing the user to update the account_id (composite key) how can I create a where clause while doing update?
Below is my table design.
CREATE TABLE accounts (
user_id VARCHAR2(20) NOT NULL,
account_id VARCHAR2(20) NOT NULL,
account_name VARCHAR2(20) NOT NULL
)
ALTER TABLE accounts ADD CONSTRAINT
uk_myTable_1 UNIQUE (User_id, account_id)
Upvotes: 0
Views: 9579
Reputation: 35353
The account_id
and user_id
values remain until you change them.
Thus. if it starts out as user_id
10 with account_id
20 and you change account_id
to 30 e.g.
UPDATE accounts
SET account_id = '30'
WHERE account_id = '20'
AND user_id = '10';
While the above works for situations where you retain all values of your composite key, I'm all for an immutable key behind the scenes that is never shared or displayed. It eliminates problems with foreign key relationships and updates to parts of composite keys. To implement this, add a field (AccountID) which is the PK, and used in FK relationships.
Alternatively, Oracle can do something similar to this by using ROWSCN and in stateless environments rowdependencies on all tables. The problem you can run into with use if just AccountID is that its possible, in a multi user environment, to have person a make update to record 1 and user 2 to be editing record 1 and then save; overwriting user 1's updates. For this is the reason I mentioned ROWSCN and ROWDEPENDENCIES. However, if you are in a state aware environment, this would be overkill.
Lastly, you could change the order of operations to execute the update to the database BEFORE you update your class. This way you have both values.
Upvotes: 3
Reputation: 57093
The unit of work in SQL is the row, not the column.
Now don't actually run this but consider that:
UPDATE accounts
SET user_id = account_id,
account_id = user_id ;
would successfully swap user_id
and account_id
for all users. This is because all rows affected by the update (as determined by the WHERE
clause) are updated all at once.
Upvotes: 0
Reputation: 23984
UPDATE accounts
SET whatevercolumnsyouwantasnormal
WHERE USER_ID = 'whatever' and ACCOUNT_ID = 'whatever2'
Obviously this will require you to track the original USER_ID and ACCOUNT_ID (separately from the new ones) until you issue the UPDATE.
If this is a web app (in particular), be sure to consider the case where the user triggers this process twice in quick succession.
Alternatively, look into Oracle's RowID. You could use it in the WHERE clause instead of the PK columns.
Upvotes: 0
Reputation: 15125
Try this:
UPDATE accounts SET account_id = New_account,account_name = new_name
WHERE user_id = Old_id and account_id = old_account_id
Upvotes: 0