Need to update column based on records returned

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to update column based on records returned

Author

Topic

mcfarcw
Starting Member

2 Posts

Posted - 2010-05-17 : 10:52:31

I need to update the data_client_tb.support_lifetime_limit_nr to 2000 for each record returned in the query below.
I have been unable to get an update statement to work correctly to do this.

Can you help?

SELECT DISTINCT
proc_obligation_tb.client_id, data_client_tb.first_name_tx, data_client_tb.last_name_tx, data_client_tb.pin_tx, proc_obligation_tb.fund_source_id,
data_client_tb.lifetime_limit_nr, data_client_tb.support_lifetime_limit_nr, data_client_tb.status_id
FROM proc_obligation_tb INNER JOIN
data_client_tb ON proc_obligation_tb.client_id = data_client_tb.client_id
WHERE (proc_obligation_tb.fund_source_id = 30) AND (data_client_tb.status_id = 1) AND (data_client_tb.lifetime_limit_nr < 20000)
ORDER BY proc_obligation_tb.client_id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-17 : 10:57:49

Begin trasaction

Update client_tb
set support_lifetime_limit_nr=2001
FROM proc_obligation_tb WITH (nolock) INNER JOIN
data_client_tb as client_tb ON proc_obligation_tb.client_id = client_tb.client_id
WHERE (proc_obligation_tb.fund_source_id = 30) AND (client_tb.status_id = 1) AND (client_tb.lifetime_limit_nr < 20000)

--Write a select query here to check if only required data are updated

-- If correctly updated,run the below

Commit transaction

-- If not updated corrected, run the below

Rollback transaction

Madhivanan

Failing to plan is Planning to fail