April 23, 2007 at 4:02 pm
I am receiving the following error when attempting to update a column with values in another table. The script is as follows and I'm not sure how to fix the problem. I am using a SQL view as the source data to update the table. Any help would be appreciated:
Error Message:
Server: Msg 512, Level 16, State 1, Procedure ATEC_PriceStatus_UPSERT, Line 5
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.
SQL Script I'm using:
UPDATE IV00105 SET LISTPRCE = MSRP
FROM Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG
INNER JOIN IV00105 ON
RTRIM(IV00105.ITEMNMBR) = RTRIM(Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG.ProductNumber)
WHERE
Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG.USCATVLS_3 LIKE '1.%'
AND Advanced_Test_Equipment_Corporation_MSCRM.dbo.vATEC_ProductBase_ProductExtension_IV00101_EG.MSRP > = IV00105.LISTPRCE
AND IV00105.LISTPRCE =0
When I run the Select statement and the results appear fine and are disclosed as follows:
0.0000 0 VARI-VZS6953G5JM 200W 2-4GHz TWT Amplifier .00000 1.d. .0000 .0000 VARI-VZS6953G5JM 0 3 .00000 9807
0.0000 0 ITHA-3921 10Hz-15kHz Lock-In Amplifier .00000 1. .0000 .0000 ITHA-3921 0 3 .00000 7268
0.0000 0 MINI-TIA-900-10 100MHz-900MHz 10W RF Amplifier, 28dB Gain, BNC .00000 1.e. .0000 .0000 MINI-TIA-900-10 0 3 .00000 8022
0.0000 0 MICR-LAB1C-2040-5 2-4GHz 5W Solid State Amplifier, Type N .00000 1.d. .0000 .0000 MICR-LAB1C-2040-5 0 3 .00000 8031
4500.0000 1995 AMPL-75A220 10kHz-220MHz 75W RF Amplifier .00000 1.e. .0000 .0000 AMPL-75A220 0 3 .00000 8183
6825.0000 2006 AGIL-83017A 500M-26.5GHz Microwave System Amplifier 25dB/18dBm .00000 1.d. .0000 .0000 AGIL-83017A 0 3 .00000 8866
0.0000 0 AHSY-PAM-0118 20MHz-18GHz Preamplifier, 38dB gain, Type N .00000 1.d. 995.0000 .0000 AHSY-PAM-0118 0 3 .00000 9565
0.0000 0 AMPL-10HA 225-410MHz 10W RF Amplifier .00000 1.e. .0000 .0000 AMPL-10HA 0 3 .00000 7472
0.0000 0 AGIL-489A 1-2GHz 1W TWT Amplifier .00000 1.d. .0000 .0000 AGIL-489A 0 3 .00000 7295
0.0000 0 PACI-8255 Bridge Transducer Amplifier Module, Reqs Mainframe .00000 1. .0000 .0000 PACI-8255 0 3 .00000 7613
0.0000 0 VARI-VZS6953G5EM 200W 2-4GHz TWT Amplifier .00000 1.d. .0000 .0000 VARI-VZS6953G5EM 0 3 .00000 9828
0.0000 0 KALM-737LCS-CE 10kHz-1GHz 30W RF Amplifier, 45dB gain .00000 1.e. 1072.5000 .0000 KALM-737LCS-CE 0 3 .00000 8375
0.0000 0 AMPL-100W1000M1 80MHz-1GHz 100W RF Amplifier, 50dB,Type N, w/Mtr .00000 1.e. .0000 .0000 AMPL-100W1000M1 0 3 .00000 8223
0.0000 0 EGG-5209 0.5Hz-120kHz Lock-In Amplifier, 100nV-3V FullScale .00000 1. 325.0000 .0000 EGG-5209 0 3 .00000 9535
April 23, 2007 at 4:11 pm
your code does not show a subquery.
typically this happens when a query is constructed this way.
Select *
From Mytable
Where MyCol = (select SomeCol
from ATable
Where Value = 'This')
If the subquery returns more than 1 row the select will fail, unless you use the "IN" clause instead of the "="
Since you say your inserting into a view, Is there perhaps a trigger using a subquery in this manner?'
April 23, 2007 at 5:18 pm
The SQL View I'm using is as follows. I'm trying to update data from this SQL view into another table as described in the original posted UPDATE SQL script. I'm just starting to learn SQL so I'm not sure if and how a SQL trigger maybe impacting this view when trying to use it in an Update statement.
CREATE VIEW dbo.vATEC_ProductBase_ProductExtension_IV00101_EG
AS
SELECT TOP 100 PERCENT CASE WHEN Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFCmfg_last_list_price IS NOT NULL
THEN CONVERT(varchar, CONVERT(money, Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFCmfg_last_list_price), 2)
ELSE CONVERT(varchar, CONVERT(money, 0), 2) END AS MSRP,
CASE WHEN Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFImfg_last_list_year IS NOT NULL
THEN Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.CFImfg_last_list_year ELSE 0 END AS MLLY,
Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase.ProductNumber, ATEC.dbo.IV00101.ITEMDESC, ATEC.dbo.IV00105.LISTPRCE,
ATEC.dbo.IV00101.USCATVLS_3, ATECREPORTS.dbo.ITEMREPORT.monthly, ATECREPORTS.dbo.ITEMREPORT.sale
FROM Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase INNER JOIN
Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase ON
Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductExtensionBase.ProductId = Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase.ProductId
INNER JOIN
ATEC.dbo.IV00101 ON Advanced_Test_Equipment_Corporation_MSCRM.dbo.ProductBase.ProductNumber = ATEC.dbo.IV00101.ITEMNMBR INNER JOIN
ATEC.dbo.IV00105 ON ATEC.dbo.IV00101.ITEMNMBR = ATEC.dbo.IV00105.ITEMNMBR INNER JOIN
ATECREPORTS.dbo.ITEMREPORT ON ATEC.dbo.IV00101.ITEMNMBR = ATECREPORTS.dbo.ITEMREPORT.ITEMNMBR
WHERE (ATEC.dbo.IV00101.ITMCLSCD = 'INVENTORY')
April 24, 2007 at 1:57 pm
Well
the error message indicates the error is bubbling up from
Server: Msg 512, Level 16, State 1, Procedure ATEC_PriceStatus_UPSERT, Line 5
Can you post the entire procedure.
April 25, 2007 at 9:55 am
I have received a similar error message when trying to update a table that has a trigger. If you have a trigger on that table, you must stop it and restart it when the procedure is finished.
April 25, 2007 at 12:29 pm
Thanks! The trigger on the table appears to be the problem. You mention to stop the trigger and then restart it when the procedure is finished. I'm a novice SQL user and not too familiar with Triggers. Can I build it into my existing script to do that? If so, how do I go about doing that.
April 25, 2007 at 12:42 pm
I figured out how to Disable and Enable the trigger on the table. Thanks so much for everyone's help.
April 27, 2007 at 4:47 pm
That's a bit, well, insane... why is the trigger there in the first place if you're going to just disable it? Either the trigger is bad or your code is bad. Fix it instead of patching it with disabling a trigger.
--Jeff Moden
April 27, 2007 at 5:13 pm
In my case the trigger is there to effect MANUAL data changes - if those changes are not needed and another corrective change is, the trigger must be disabled and enabled after the needed changes.
April 27, 2007 at 7:34 pm
Then, the trigger is wrong... it should be able to handle both.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply