insert and then update... - 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 2005 Forums

Transact-SQL (2005)

insert and then update...

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-20 : 09:13:12

Hi

I have this stored procedure that insert x number of rows from tbl_ShoppingCart to tbl_Order, this works just fine.


BEGIN
INSERT INTO tbl_Order (ShoppingCartID, CartID, NodeID, Quantity) 

SELECT CartID, @CartID, NodeID, Quantity FROM tbl_ShoppingCart WHERE UserUniqueID=@UID
END

But now I need to also make a update like this..

UPDATE tbl_Product SET Storage = (Storage - Quantity) WHERE NodeID = xx

Can this be done in the same way, so that each row that is inserted to tbl_Order also update the Storage Column in tbl_Products?

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-05-20 : 12:46:52

quote:

Can this be done in the same way,

Hi,

Why not you try and post the result if you are facing an error

I think, your taks can be achieved without using WHILE. You can use UPDATE statements, in a query with JOIN.

Thanks
Lijo

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-20 : 13:06:48

I don't have any errors, I just need help on how to modify my exsisting code so that a update also is done as described above...

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-20 : 14:07:16

what is wrong with this?

BEGIN
INSERT INTO tbl_Order (ShoppingCartID, CartID, NodeID, Quantity)
UPDATE tbl_Product SET Storage = (Storage - Quantity) WHERE NodeID = xx
SELECT CartID, @CartID, NodeID, Quantity FROM tbl_ShoppingCart WHERE UserUniqueID=@UID
END

I think you should explain what you want in more detail.

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-20 : 14:23:16

In the original Insert code the values that are inserted come from the select statement, but what about the Quantity and the NodeID value in the Update statement, how do I get thoose values from the select statement (from the tbl_ShoppingCart)?

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-21 : 07:37:11

I would really appreciate if someone could show how I should solve this... I'm a bit lost.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 08:05:55

Try the below statement..
In case you are not getting the required output then i suggest that you post the table structure and some sample data.

BEGIN

INSERT INTO tbl_Order (ShoppingCartID, CartID, NodeID, Quantity)
SELECT CartID, @CartID, NodeID, Quantity FROM tbl_ShoppingCart WHERE UserUniqueID=@UID

Update tbl_Product
Set Storage = (tp.Storage - ts.Quantity)
from tbl_Product tp join tbl_ShoppingCart ts on
tp.NodeID = ts.NodeID and ts.UserUniqueID=@UID

END

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-21 : 08:18:57

Hi pk_bohra, Thank you very much"

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 08:52:47

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources