Update Query Help - SQL Server Forums

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 Query Help

Author

Topic

vk18
Posting Yak Master

146 Posts

Posted - 2006-10-18 : 21:02:37

Hello Friends,

I am trying to update the data from one table to another table. The following query doing is, It is updating the tblspot values with only the last row of tblinvoice, even though i am using DataReader.I want to go through the 1st row of tblinvoice and update the 1st row of tblspot and 2nd row of tblinvoice with 2nd row of tblspot and so on. can any one tell me what is wrong with this..?
Thx

Update tblspot set fkinvoice = select tblinvoice.pkid, fkinvoicenumber = tblinvoice.invoicenumber,fkinvoiceextension = 0 from tblspot inner join tblinvoice on tblspot.fkgroup = '" & Me.txtgroup.Text & "' and tblspot.fksubgroup = '" & Me.txtsubgroup.Text & "' and tblspot.datescheduled between '" & Me.txtStartDate.Text & "' and '" & Me.txtEndDate.Text & "'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-18 : 22:24:40

You need a proper join in your update statement. The code below shows the general form of what you need to do.


Update s
set
	SomeColumn_01 = i.SomeInvoiceColumn_01,
	SomeColumn_02 = i.SomeInvoiceColumn_02,
	... and so on ...
from
	tblspot s
	inner join
	tblinvoice i
	on s.SpotJoinColumn = i.InvJoinColumn

CODO ERGO SUM

vk18
Posting Yak Master

146 Posts

Posted - 2006-10-19 : 00:31:05

Hi,
If i build the query with the Where Clause like this, I mean going through each row in tblinvoice and update the tblspot...?
Thx

Update tblspot set fkinvoice = tblinvoice.pkid, fkinvoicenumber = tblinvoice.invoicenumber,
fkinvoiceextension = 0 from tblspot inner join tblinvoice on tblspot.fkdetail = tblinvoice.pkid where
tblspot.fkgroup = '" & Me.txtgroup.Text & "' and tblspot.fksubgroup = '" & Me.txtsubgroup.Text & "' and tblspot.datescheduled between '" & Me.txtStartDate.Text & "'
and '" & Me.txtEndDate.Text & "'

vk18
Posting Yak Master

146 Posts

Posted - 2006-10-19 : 11:36:32

Hi,

I don't have any join column in these two tables. Is there any way that i can update the values from tblinvoice into tblspot.
Thx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-21 : 07:40:48

Without related column, how do you want to update?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources