| Author |
Topic |
|
MorrisK
Yak Posting Veteran
83 Posts |
 Posted - 2005-11-02 : 16:44:50
|
| I want to update values in one table with values from another table. I have an update trigger on the table I'm updating and when I run my update I get the "Subquery returned more than 1 value." message referencing the trigger.I thought I would try modifying my update rather than my trigger because it seems that this situation should normally not occur. In other words only one record should normally be updated at a time.I need to update BuyerCodes in a ItemMaster table to match the BuyerCode in the ItemClass table where ItemMaster.BuyerCode <> ItemClass.BuyerCodeItemMaster tableItem ItemClass ItemCategory BuyerCode123 C1 B B1456 C1 B B1789 C1 B B2abc C2 B B3def C1 M B1ItemClass table ItemClass BuyerCodeC1 B2C2 B3In this example, I need to update Items 123 and 456. Here is what I'm using but I get the error because of the multiple rows to update.UPDATE ItemMasterSET Buyer = ItemClass.BuyerFROM ItemMaster, ItemClassWHERE ItemMaster.ProductClass = ItemClass.ProductClassAND ItemMaster.ItemCategory = 'B' AND LEN(ItemClass.Buyer)<>0AND (LEN(ItemMaster.Buyer)=0 OR ItemMaster.Buyer<>ItemClass.Buyer)If I wanted to change the sql for the UPDATE above so that it updates the rows one at a time, how would I do it?Or would it be easier to change my trigger to handle multiple row updates? If this is the case, would I use a cursor to loop through the updated records?Thanks in advance,Kevin |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-11-02 : 19:02:18
|
| Can you post the trigger code?ThanksNathan Skerl |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-11-03 : 04:18:27
|
| It would be better to use a stored proceedure. Like nathans said, paste your code here |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-03 : 09:40:06
|
quote:
...Or would it be easier to change my trigger to handle multiple row updates? If this is the case, would I use a cursor to loop through the updated records?
ALWAYS write triggers to handle multi-record transactions.AVOID putting cursors in triggers.To handle multi-record transactions, your trigger should make use of the virtual "inserted" and "deleted" tables.Writing triggers that can only handle single-record transaction is, in my opinion, one of the worst coding transgressions anyone can make. |
|
|
|
MorrisK
Yak Posting Veteran
83 Posts |
Posted - 2005-11-03 : 10:03:48
|
| Here is the trigger that is giving the error. In our company, bought-out items are supposed to have TraceableType = 'T', except if they have ItemClass = 'C2'. This trigger checks new items for this as they are added and lets us know if they are not.IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BoughtOutNotTraceableINSERT' AND type = 'TR') DROP TRIGGER BoughtOutNotTraceableINSERTGOCreate Trigger BoughtOutNotTraceableINSERTOn dbo.ItemMasterFor INSERTAsIF ((SELECT ins.ItemCategory FROM inserted ins) = 'B' AND (SELECT ins.ItemClass FROM inserted ins) <>'C2' AND (SELECT ins.TraceableType FROM inserted ins) <>'T') BEGIN declare @From varchar(90), @To varchar(90), @Subject varchar(100), @Body varchar(4000), @Item varchar(30), @Descr varchar(30), @DBNumber varchar(128), @Oper varchar(6) select @Item = (SELECT RTRIM(ins.Item) FROM inserted ins), @Descr = (SELECT RTRIM(ins.Description) FROM inserted ins), @DBNumber = (SELECT RIGHT(DB_NAME(),1)), @Oper = (SELECT TOP 1 ItemMastAmendJnl.OperatorCode FROM ItemMastAmendJnl, inserted ins WHERE ItemMastAmendJnl.Item= ins.Item ORDER BY ItemMastAmendJnl.JnlDate DESC , ItemMastAmendJnl.JnlTime DESC), @From = 'my-email@my-company.com', @To = 'their-email@my-company.com', @Subject = 'Bought-out item, '+@Item+', added and not set as lot traceable', @Body = @Item+', '+@Descr+', ', @Body = @Body+'was inserted into the ItemMaster table in the company ', @Body = @Body+@DBNumber+' database as a bought-out item but was not set as lot traceable. ', @Body = @Body+'This item was added by operator '+@Oper+'.' exec sp_send_cdosysmail @From,@To,@Subject,@Body ENDGOHow can I modify it to handle multiple row updates without using a cursor?Kevin |
|
|
|
MorrisK
Yak Posting Veteran
83 Posts |
Posted - 2005-11-07 : 18:23:13
|
Suggestions anyone? I'm not sure what to do. blindman says quote:
To handle multi-record transactions, your trigger should make use of the virtual "inserted" and "deleted" tables.
I'm using inserted and deleted but how do you handle multiple records?Thanks,Kevin |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-07 : 18:28:30
|
| You will need to loop through every row in the inserted table using a WHILE loop or a cursor. You have to loop due to needing to call sp_send_cdosysmail for each row. You can not do this set-based.Tara Kizer |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-11-07 : 19:16:32
|
| I'm having a similar issue. I need a trigger that will recurse up a tree stored in a table looking for circular references. I think this requires a WHILE loop in order to check every row inserted or updated.I figure I can put the WHILE loop in a UDF and call the UDF in the SELECT clause. My plan was to raise an error when a circular reference was found but its saying 'Invalid use of 'RAISEERROR' within a function.'.Any ideas?Also how does one WHILE loop over all the rows in the inserted table? Something like this....SELECT TOP 1 * FROM inserted WHERE PK > @lastPK |
|
|
|
alexsts2010
Starting Member
3 Posts |
Posted - 2011-12-09 : 14:25:33
|
| correction to update statement to narrow it down to those 2 rows:update ItemMaster tset t.buyerCode= IC.buyerCodefrom ItemClass IC wheret.itemClass=IC.ItemClass and t.item in (select item from ItemClass where BuyerCode='B1') and t.ItemCategory='B'AB |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
UPDATE Multiple Rows-one at a time