CODEWITHSUNDEEP
sqloracle-database
Cigo
Cigo

Reputation: 1

Update query that will set specific columns in a record with values from another record of same table

look at this query; 

update user_data set old_status= 'SNNNNS',
                     user_group='15',
                     default_rate='DEFAULT',
                     entity_num='1001'
where user_name='Dasu';

I know I could write the query like that and get result, but I dont want to be writing the values. These values are from another record with user_name 'sys' in the same table. I want a query that will update these particular columns in 'Dasu' with values from 'sys'.

Any idea?

Upvotes: 0

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

In Oracle, you can use merge with a self-join. Alternatively, you can write correlated subqueries in the update:

update user_data
    set (old_status, user_group, default_rate, entity_num) = (select old_status, user_group, default_rate, entity_num from user_data where user_name = 'sys')
where user_name='Dasu';

Upvotes: 1

chxzy
chxzy

Reputation: 489

You can nest a select in your update statement. For Example:

UPDATE Dasu
SET old_status = (SELECT old_status FROM sys WHERE Id = 1)

Upvotes: 0

Related Questions