- Mysql Trigger Update Multiple Rows Sql Download
- Update Multiple Records Sql
- Update Multiple Rows Sql
- Mysql Trigger Update Multiple Rows Sql
Good day,
SQL - Update multiple records in one query. Ask Question 96. I have table - config. This SQL update existing record and insert new (2 in one) share| improve this answer. Can I concatenate multiple MySQL rows into one field?
I am relatively new to triggers. I thought I new what I was doing (first mistake) until an update trigger contained more than 1 row.
This is for a system that I did not design but inherited. I need to make the minimum changes necessary.
For historical reasons whenever an invoice is created a duplicate of the vendor records is created (and the duplicates are not visible in vendor dropdowns). Currently, when the user changes the vendor code to the visible record (the original) it is no longer linked to the copies and searching for invoices for the new vendor code will not reveal any old data.
So I added 2 columns, OrigVenCode and OrigVenID to setup the relationship. (I don't know if the origvencode will ever be of use but I want to retain it just in case)
Table layout (last 2 columns are new)
vendorID VendorCode IsCopy OrigVenCode OrigVenID
----------- ------------ ---------- ------------- -----------
1 ABC 0 ABC 1
2 ABC 1 ABC 1
3 ABC 1 ABC 1
4 DEF 0 HHH 4
5 DEF 1 HHH 4
So I need a trigger (update?) that when the vendor code of the original record (indicated by IsCopy = 0) is changed that all the copies are changed as well.
For instance if I edit vendorID 1 and set the vendorcode = xxx I want the vendorcode for vendorID 2 and vendorID 3 changed as well.
Mysql Trigger Update Multiple Rows Sql Download
Too me this looked very easy until the case where multiple records are modified within a transaction and I get the beloved:
'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.'
error. How do I take into account multiple rows in the update?
Update Multiple Records Sql
Here is my trigger:
CREATE TRIGGER Trig_vendor_code ON [vendor] FOR UPDATE
AS
DECLARE @newcode varchar(10)
DECLARE @venid int
DECLARE @iscopy int
SELECT @venid = (SELECT vendorID FROM Inserted)
SELECT @iscopy = (SELECT IsCopy FROM Inserted)
--only care if we are working on an original
IF (@iscopy = 1)
BEGIN
RETURN
END
--if vendor.code wasn't update, exit
IF NOT UPDATE(vendorCode)
BEGIN
RETURN
END
SELECT @newcode = (SELECT vendorCode FROM Inserted)
UPDATE vendor SET vendorcode = @newcode WHERE origvenid = @venid and IsCopy = 1
Update Multiple Rows Sql
Any help would be much appreciated. I hope you understand my example. And sorry, I did not design the messy structure. Just trying to prevent further data loss.
Mysql Trigger Update Multiple Rows Sql
-Markus