update multiple values from a select

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

SQL Server 2000 Forums

Transact-SQL (2000)

update multiple values from a select

Author

Topic

rutherford218
Starting Member

7 Posts

Posted - 2005-06-08 : 22:05:59

I have a select statement that returns a bunch of AuthId's from a table. I want all of those AuthId's put into a table called Transaction. The select has multiple joins and returns multiple values, I want a way to put multiple values in the table.

here's what I have how can I do this....

update Transactions set tr.PmtInfo2 = (select orb.AuthId from order_bridge orb
join Transactions tr on tr.TrxId = orb.TrxId
join Transactions trx (nolock) on tr.TrxId = trx.ApplyToTrxId
where tr.TrxTypeId = 1 and orb.PaymentType = 1 and trx.TrxTypeId = 2)

Thanks in Advance
Brian

rutherford218
Starting Member

7 Posts

Posted - 2005-06-08 : 23:28:58

How about it... I hit the books and found the answer, turns out it was this easy.

update tr set tr.PmtInfo2 = orb.AuthId from order_bridge orb
join Transactions tr on tr.TrxId = orb.TrxId
join Transactions trx (nolock) on tr.TrxId = trx.ApplyToTrxId
where tr.TrxTypeId = 1 and orb.PaymentType = 1 and trx.TrxTypeId = 2

Thanks,
Brian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-09 : 00:46:26

Yes. Using Join is the best approach.

quote:

update Transactions set tr.PmtInfo2 = (select orb.AuthId from order_bridge orb
join Transactions tr on tr.TrxId = orb.TrxId
join Transactions trx (nolock) on tr.TrxId = trx.ApplyToTrxId
where tr.TrxTypeId = 1 and orb.PaymentType = 1 and trx.TrxTypeId = 2)

If select query returns more than one value, you will get error

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources