UPDATE SD_INST 
SET SD_INST.tme_admin_id = '1357511188.1.880#TMF_Administrator::Configuration_GUI#',
    SD_INST.sd_Action = 'install (f)',
    SD_INST.Exec_Time = '2000-01-01 01:00',
    SD_INST.State = 'IC---',
    SD_INST.Record_Time = GetDate() 
FROM MATCH_SWARE_VIEW M 
     JOIN SD_INV S
         ON M.SWARE_DESC + M.SWARE_VERS = S.Inventory_Name 
        AND M.Path = S.Directory_Path 
     JOIN SD_INST
         ON SD_INST.computer_sys_id = M.computer_sys_id 
        AND SD_INST.sware_name = S.Inventory_Name 
        AND SD_INST.sware_vers = S.Inventory_Vers 
WHERE SD_INST.State <> 'IC---'

quote:

---

Originally posted by samrat

Team,

Can you see why the following query would return this error? I am not using a subquery?



ERROR:

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.





QUERY:



UPDATE SD_INST

SET SD_INST.tme_admin_id = '1357511188.1.880#TMF_Administrator::Configuration_GUI#',

SD_INST.sd_Action = 'install (f)',

SD_INST.Exec_Time = '2000-01-01 01:00',

SD_INST.State = 'IC---',

SD_INST.Record_Time = GetDate()

FROM SD_INST ,

( SELECT M.Computer_Sys_Id, S.Inventory_Name, S.Inventory_Vers

FROM MATCH_SWARE_VIEW M INNER JOIN SD_INV S

ON M.SWARE_DESC + M.SWARE_VERS = S.Inventory_Name AND

M.Path = S.Directory_Path ) B

WHERE SD_INST.computer_sys_id = B.computer_sys_id AND

SD_INST.sware_name = B.Inventory_Name AND

SD_INST.sware_vers = B.Inventory_Vers and

SD_INST.State <> 'IC---'



Samrat

---

quote:

---

Originally posted by samrat

Kristen, do you think it is some sort of a bug?

I converted my original query to the following and still got similar result

UPDATE A
SET A.tme_admin_id = '1357511188.1.880#TMF_Administrator::Configuration_GUI#',
A.sd_Action = 'install (f)',
A.Exec_Time = '2000-01-01 01:00',
A.State = 'IC---',
A.Record_Time = GetDate()
FROM SD_INST A
WHERE EXISTS ( Select 1 FROM
( SELECT M.Computer_Sys_Id, S.Inventory_Name, S.Inventory_Vers
FROM MATCH_SWARE_VIEW M
INNER JOIN SD_INV S
ON M.SWARE_DESC + M.SWARE_VERS = S.Inventory_Name AND
M.Path = S.Directory_Path
) B
WHERE A.computer_sys_id = B.computer_sys_id AND
A.sware_name = B.Inventory_Name AND
A.sware_vers = B.Inventory_Vers
)
AND A.State <> 'IC---'



Samrat

---

quote:

---

The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated (in other words, if the UPDATE statement is not deterministic). For example, given the UPDATE statement in the following script, both rows in table s meet the qualifications of the FROM clause in the UPDATE statement, but it is undefined which row from s is used to update the row in table t.

CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO

The same problem can occur when combining the FROM and WHERE CURRENT OF clauses. In this example, both rows in table t2 meet the qualifications of the FROM clause in the UPDATE statement. It is undefined which row from t2 is to be used to update the row in table t1.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT)
GO
CREATE TABLE t2(d1 INT PRIMARY KEY, d2 INT)
GO
INSERT INTO t1 VALUES (1, 10)
INSERT INTO t2 VALUES (1, 20)
INSERT INTO t2 VALUES (2, 30)
go

DECLARE abc CURSOR LOCAL FOR
SELECT * FROM t1

OPEN abc

FETCH abc

UPDATE t1 SET c2 = c2 + d2
FROM t2
WHERE CURRENT OF abc
GO

---

( SELECT MAX(M.Computer_Sys_Id) AS Computer_Sys_Id,
         MAX(S.Inventory_Name) AS Inventory_Name,
         MAX(S.Inventory_Vers) AS Inventory_Vers 
FROM MATCH_SWARE_VIEW M INNER JOIN SD_INV S
ON M.SWARE_DESC + M.SWARE_VERS = S.Inventory_Name AND
M.Path = S.Directory_Path 
) B