Update & Select Error - 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

Transact-SQL (2000)

Update & Select Error

Author

Topic

jj6849
Starting Member

8 Posts

Posted - 2008-04-29 : 17:45:31

I can run the inner SELECT query just fine, but when I add the UPDATE statement around the select, query analyzer throws:
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.

We're running: SQL2000 8.00.2039 SP4 Standard Edition

I've searched other forums and MS site, but haven't found any solutions that I believe pertain to this. Any ideas/thoughts are greatly appreciated:
(the 2nd update query below works)

UPDATE ac_orders..mech_master
SET status = 'R', reject_codes = coalesce(b.reject_codes,'') + ',4001 xxUSOC requires at least 4 required vertical services'
FROM
(
-- inner select query starts here
SELECT
a.masterID,
ISNULL((select detail_value from ac_orders..mech_raw_test2 where masterid=a.masterid and product_code = 4001 and substring(detail_code,3,3) ='CBS' and substring(detail_code,1,2)=substring(b.detail_code,1,2)),0) as CBSUSOC,
ISNULL((SELECT top 1 ISNULL(masterid,0) from mech_raw_test2 where masterid=a.masterid and product_code = 4001 and substring(detail_code,3,4) ='USOC' and substring(detail_code,1,2)=substring(b.detail_code,1,2) and detail_value like '%qwe%'),0) +
ISNULL((SELECT top 1 ISNULL(masterid,0) from mech_raw_test2 where masterid=a.masterid and product_code = 4001 and substring(detail_code,3,4) ='USOC' and substring(detail_code,1,2)=substring(b.detail_code,1,2) and detail_value like '%wmc%'),0) +
ISNULL((SELECT top 1 ISNULL(masterid,0) from mech_raw_test2 where masterid=a.masterid and product_code = 4001 and substring(detail_code,3,4) ='USOC' and substring(detail_code,1,2)=substring(b.detail_code,1,2) and detail_value like '%e4r%'),0) as usoctotal,
(a.masterid * 2) as masteridtotal
FROM
ac_orders.dbo.mech_master a
JOIN
ac_orders.dbo.mech_raw_test2 b
ON
a.masterID = b.masterID and a.status in ('L','R')and b.product_code = 4001 and b.detail_code like '%usoc' and (select count(masterid) from ac_orders..mech_raw_test2 where masterid=a.masterid and product_code = 6012) > 0
GROUP BY a.masterid, b.product_code, Substring(b.detail_code,1,2)
-- inner select query ends here
)a JOIN ac_orders..mech_master b ON a.masterid = b.masterid
WHERE usoctotal < masteridtotal AND CBSUSOC IN ('PG01Q','PG01R')

---this is the 2nd update query that works
UPDATE ac_orders..mech_master
SET status = 'R', reject_codes = coalesce(b.reject_codes,'') + ',4001 & 4002 Total must equal 6012QTYLNS'
FROM
(
SELECT a.masterID,
(select COUNT(masterid) from ac_orders.dbo.mech_raw_test2 where masterId = a.masterID AND product_code = 4001 AND RIGHT(detail_Code,2) = 'TN') +
(select COUNT(masterid) from ac_orders.dbo.mech_raw_test2 where masterId = a.masterID AND product_code = 4002 AND RIGHT(detail_Code,2) = 'TN')
as linestotal,
(select ISNULL(detail_value,0) from ac_orders.dbo.mech_raw_test2 where masterId = a.masterID AND product_code = 6012 AND detail_Code = 'QTYLNS')
as qtylinetotal
FROM ac_orders.dbo.mech_raw_test2 a
JOIN ac_orders.dbo.mech_master b
ON a.masterId = b.masterID
WHERE b.status in ('L','R') AND a.product_code = 6012 and detail_code = 'TERM'
GROUP BY a.masterID
) a JOIN ac_orders..mech_master b
ON a.masterid = b.masterid
WHERE linestotal <> qtylinetotal OR qtylinetotal <> 0 or linestotal <> 0

jj6849
Starting Member

8 Posts

Posted - 2008-04-29 : 18:18:16

Figured it out. The innser SELECT query was joining on the following:
FROM
ac_orders.dbo.mech_master a
JOIN
ac_orders.dbo.mech_raw_test2 b
ON
a.masterID = b.masterID and a.status in ('L','R')and b.product_code = 4001 and b.detail_code like '%usoc' and (select count(masterid) from ac_orders..mech_raw_test2 where masterid=a.masterid and product_code = 6012) > 0

After the a.masterID = b.master I removed the next "and", replaced with WHERE and it works now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources