How to update fields from a Select Statement

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

How to update fields from a Select Statement

Author

Topic

Big_tim
Starting Member

22 Posts

Posted - 2009-09-11 : 07:59:51

Is there any really clever way to update values from a select statement?
What I mean by this is that if a select statement (which has 3 or 4 joins) returns 30 rows where x = 1, but I want x to = 2.

I can re-write it as an Update statement (but it gets more complicated with more joins) so I just wanted to know if there are any other ways to update the values?

As an example, this script:

select --top 10
	_pledge_schedule.id
	, _pledge_schedule.reference
	, _pledge_schedule.date_created
	, _pledge_schedule.pledge_payment_id
	, _pledge_schedule.submission_date
	, _pledge_schedule.requested_date
	, _pledge_schedule.received_date
	, _pledge_schedule.supporter_id
	, _pledge_schedule.status
	, _pledge_payment_Details.request_month_date
	, (SELECT reference FROM _contact WHERE  _pledge_schedule.supporter_id = _contact.id) as Contact_Ref
	, (SELECT id FROM _pledge WHERE  _pledge_schedule.pledge_id = _pledge.id) as Pledge_ID
	, (SELECT reference FROM _Payment_Instruction WHERE _Pledge_Payment_Details.payment_instruction_id = _Payment_Instruction.id) as Payment_Instruction_Ref
	, (SELECT status FROM _Payment_Instruction WHERE _Pledge_Payment_Details.payment_instruction_id = _Payment_Instruction.id) as Payment_Instruction_Status
FROM _Pledge_Schedule
INNER JOIN _pledge
	ON _pledge_schedule.pledge_id = _pledge.id
INNER JOIN _Pledge_Payment_Details
	ON _pledge_schedule.pledge_payment_id = _Pledge_Payment_Details.id
INNER JOIN _Payment_Instruction
	ON _Pledge_Payment_Details.payment_instruction_id = _Payment_Instruction.id
WHERE  _pledge_schedule.received_date IS NULL
	and _pledge_schedule.status = 1
	and _pledge.payment_method = 3
	and _pledge.pledge_status = 1
	and _Payment_Instruction.status = 4
	and _pledge_schedule.fulfilment is null
ORDER by _pledge_schedule.requested_date DESC 
, _pledge_schedule.reference DESC

So this will return every value for _pledge_schedule.status and they are all '1'.
However, I need them to all be set to '2'.
What is the easiest way of doing that?

Cheers!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 08:21:33

Can you post your select statement?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 08:23:47

Are there unique ids to identify this rows?
You can make your existing select statement as a derived table and join it in your update statement.

update u
set x=2
from table_to_update u
join
(select t1.id from table t1 join blabla where blabla...) as dt
on dt.id = u.id

No, you're never too old to Yak'n'Roll if you're too young to die.

Big_tim
Starting Member

22 Posts

Posted - 2009-09-11 : 08:30:28

Script is now in original post....

Big_tim
Starting Member

22 Posts

Posted - 2009-09-11 : 10:51:14

I've gone a slightly different route now.
I've used the original select statement to create a temporary table.
This has allowed me to do a much simpler INSERT script with out all of the joins to sort out.

UPDATE _pledge_schedule
SET _pledge_schedule.status = 2
FROM #Temporary_Pledge_table
WHERE #tim_test.id = _pledge_schedule.id

I've tested on the dev server and all seems a-ok to me.
Any reason why this would be a bad idea?
(And yes - I've dropped the temp table afterwards!)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 10:56:09

From what/where is it running?

No, you're never too old to Yak'n'Roll if you're too young to die.

Big_tim
Starting Member

22 Posts

Posted - 2009-09-11 : 10:59:52

Yes, sorry, typo in my last post.
Script should read:


UPDATE _pledge_schedule
SET _pledge_schedule.status = 2
FROM #Temporary_Pledge_table
WHERE #Temporary_Pledge_Table.id = _pledge_schedule.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources