UPDATE...SELECT - 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

SQL Server Development (2000)

UPDATE...SELECT

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2001-12-06 : 11:10:52

Hello Again-

I am writing a bit of code that requires BULK (well not really) INSERTs and UPDATEs. The insert code was no problem.


INSERT INTO tblFinancial_BoxType (financialID, boxTypeID, 
costPerBox, costPerItem) 
SELECT @financialID, boxTypeID, costPerBox, costPerItem
FROM tblBoxType
WHERE tblBoxType.customerID=@customerID

I am having a problem writing the same code if the record already exists and all I want to do is update it. Any help?

Edited by - Nick on 12/06/2001 11:22:14

Nick
Posting Yak Master

155 Posts

Posted - 2001-12-06 : 11:37:14

I'll make it a little easier.

Woud this work?


UPDATE tblFinancial_BoxType SET boxTypeID=t.boxTypeID,
costPerBox=t.costPerBox, costPerItem=t.costPerItem 
FROM (SELECT  boxTypeID, costPerBox, costPerItem
FROM tblBoxType
WHERE tblBoxType.customerID=@customerID) t
WHERE financialID=@financialID

Thanks!

Edited by - Nick on 12/06/2001 11:42:01

Nick
Posting Yak Master

155 Posts

Posted - 2001-12-06 : 13:26:36

Someone had posted some information about using JOINs with UPDATEs, but it seems to have disappeared. Can someone throw up an example? Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-06 : 13:40:28

UPDATE tblFinancial_BoxType
SET boxTypeID=t.boxTypeID,costPerBox=t.costPerBox, costPerItem=t.costPerItem
FROM tblBoxType
where tblBoxType.customerID=@customerID
and tblFinancial_BoxType.financialID = @financialID

INSERT INTO tblFinancial_BoxType (financialID, boxTypeID, costPerBox, costPerItem)
SELECT @financialID, boxTypeID, costPerBox, costPerItem
FROM tblBoxType
WHERE tblBoxType.customerID=@customerID
and not exists (select * from tblFinancial_BoxType where financialID = @financialID)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Nick
Posting Yak Master

155 Posts

Posted - 2001-12-06 : 14:03:01

Unfortunetly, that doesn't work. It only inserts one of the box types twice. Instead of the two box types once.

quote:

UPDATE tblFinancial_BoxType
SET boxTypeID=t.boxTypeID,costPerBox=t.costPerBox, costPerItem=t.costPerItem
FROM tblBoxType
where tblBoxType.customerID=@customerID
and tblFinancial_BoxType.financialID = @financialID

INSERT INTO tblFinancial_BoxType (financialID, boxTypeID, costPerBox, costPerItem)
SELECT @financialID, boxTypeID, costPerBox, costPerItem
FROM tblBoxType
WHERE tblBoxType.customerID=@customerID
and not exists (select * from tblFinancial_BoxType where financialID = @financialID)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

izaltsman
A custom title

1139 Posts

Posted - 2001-12-06 : 14:21:16

Nick, is there a relationship of any kind between the two tables? What are the fields that relate them? In other words, how do you tell that a certain boxTypeID from tblBoxType table should be matched up to a certain financialID in tblFinancial_BoxType? Once you figure out which field(s) relate these tables, you can join the tables together to perform your update.

Nick
Posting Yak Master

155 Posts

Posted - 2001-12-06 : 14:27:27

tblFinancial_BoxType is an n-to-n relationship between tblFinancial and tblBoxType. The primary keys for tblBoxType are boxTypeID and for tblFinancial is financialID.

quote:

Nick, is there a relationship of any kind between the two tables? What are the fields that relate them? In other words, how do you tell that a certain boxTypeID from tblBoxType table should be matched up to a certain financialID in tblFinancial_BoxType? Once you figure out which field(s) relate these tables, you can join the tables together to perform your update.

izaltsman
A custom title

1139 Posts

Posted - 2001-12-06 : 16:13:02

I still can't picture how this relationship works. I could see a 1-to-n or 1-to-1 (actually, I am almost sure that a 1-to-1 relationship is what you are really after), but not an n-to-n. Unless there is a linking table somewhere you haven't mentioned yet (if there is such a table, then boxTypeID should not appear in tblFinancial_BoxType).
But regardless... The way it stands right now, it seems that boxTypeID is the best candidate to relate the two tables,
so I really don't believe that you'd want to SET this field in your UPDATE statement. Instead, you should probably join
on it. Kinda like this:


UPDATE fbt 
SET 
	fbt.costPerBox=t.costPerBox
	, fbt.costPerItem=t.costPerItem 
FROM 
	tblFinancial_BoxType fbt 
	INNER JOIN tblBoxType bt
		ON (fbt.boxTypeID = bt.boxTypeID)
where tblBoxType.customerID=@customerID 
and tblFinancial_BoxType.financialID = @financialID

Nick
Posting Yak Master

155 Posts

Posted - 2001-12-06 : 16:35:07

Here is what I'm doing. At the end of every month we send our customers a bill. All of the billing info is kept in tblFinancial. The billing information is based on all of the orders for the month and the types of boxes the orders were filled with (Box Size A, B, C). We keep all of the box types and their associated costs in tblBoxTypes. At the end of the month we want to save the costs of all the different box types for that month, because they might change the next month, and if we need to rerun the invoicing and for reporting sake, we need to know how much we were charging them that month. We bill multiple customers a month and each customer can have multiple box types. That is why we have a n-to-n relationship. Each month we have multiple financial inserts and each one has many box types associated with it.

I am having the same problem with the SQL Statement you gave me!

quote:

I still can't picture how this relationship works. I could see a 1-to-n or 1-to-1 (actually, I am almost sure that a 1-to-1 relationship is what you are really after), but not an n-to-n. Unless there is a linking table somewhere you haven't mentioned yet (if there is such a table, then boxTypeID should not appear in tblFinancial_BoxType).
But regardless... The way it stands right now, it seems that boxTypeID is the best candidate to relate the two tables,
so I really don't believe that you'd want to SET this field in your UPDATE statement. Instead, you should probably join
on it. Kinda like this:

UPDATE fbt
SET
fbt.costPerBox=t.costPerBox
, fbt.costPerItem=t.costPerItem
FROM
tblFinancial_BoxType fbt
INNER JOIN tblBoxType bt
ON (fbt.boxTypeID = bt.boxTypeID)
where tblBoxType.customerID=@customerID
and tblFinancial_BoxType.financialID = @financialID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources