cine poate sa-mi explice dece nu este corecta urmatoarea interogare?

UPDATE ANAPAT...buletin
SET id_prescriptii_pacient =
(
SELECT prescriptii_pacienti.id FROM ANAPAT...prescriptii_pacienti, ANAPAT...buletin
WHERE buletin.id_pacient = prescriptii_pacienti.id_pacient
)
WHERE EXISTS
(
SELECT buletin.id FROM anapat...buletin, anapat...prescriptii_pacienti
WHERE buletin.id_pacient=prescriptii_pacienti.id_pacient
)

Interogarea este greşită din acelaşi motiv pentru care este greşită următoarea interogare:

CREATE TABLE A (ID INT PRIMARY KEY, X INT)

CREATE TABLE B (ID INT PRIMARY KEY, Y INT)

 

INSERT INTO A VALUES (1, 10)

INSERT INTO A VALUES (2, 20)

INSERT INTO A VALUES (3, 30)

 

INSERT INTO B VALUES (1, 0)

INSERT INTO B VALUES (2, 0)

INSERT INTO B VALUES (4, 0)

 

UPDATE B SET Y=(

      SELECT X FROM A, B WHERE A.ID=B.ID

) WHERE EXISTS (

      SELECT X FROM A, B WHERE A.ID=B.ID

)

 

SELECT * FROM B

 

DROP TABLE A,B

Mai precis, motivul este că nu există nicio corelare între tabela B din clauza UPDATE şi tabela B din subquery-uri. Din acest motiv, apare eroarea "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Interogarea ar putea fi scrisă corect în unul din următoarele feluri:

UPDATE B SET Y=(

      SELECT X FROM A WHERE A.ID=B.ID

) WHERE EXISTS (

      SELECT X FROM A WHERE A.ID=B.ID

)

 

UPDATE B SET Y=X

FROM A WHERE A.ID=B.ID

 

UPDATE B SET Y=X

FROM A, B WHERE A.ID=B.ID

 

UPDATE B SET Y=X

FROM A INNER JOIN B ON A.ID=B.ID

 

Menţionez că doar prima variantă este acceptată de standardul ANSI-SQL. După câte ştiu, celelalte trei variante (cele care conţin sintaxa UPDATE FROM) funcţionează doar în Microsoft SQL Server şi Sybase (nu şi în Oracle sau DB2).

(later edit:)

De asemenea, este important de precizat că dacă există o eroare în date (sau în condiţia de corelare dintre A şi B), atunci prima variantă ne avertizează cu aceeaşi eroare menţionată mai sus, însă celelalte trei variante aleg la întâmplare una din valori şi fac un UPDATE care ar putea fi incorect. De exemplu, să considerăm următoarele date:

CREATE TABLE A (ID INT /*PRIMARY KEY*/, X INT)

CREATE TABLE B (ID INT PRIMARY KEY, Y INT)

 

INSERT INTO A VALUES (1, 10)

INSERT INTO A VALUES (2, 20)

INSERT INTO A VALUES (2, 21)

INSERT INTO A VALUES (3, 30)

 

INSERT INTO B VALUES (1, 0)

INSERT INTO B VALUES (2, 0)

INSERT INTO B VALUES (4, 0)

 

Pentru aceste date, nu este clar dacă rândul cu ID-ul 2 din tabela B ar trebui completat cu valoarea 20 sau 21. Prima variantă (cea cu subquery) ne dă eroare, avertizându-ne despre problema, dar celelalte trei variante (cele cu UPDATE FROM) aleg la întâmplare o valoare (de exemplu 20) şi executa actualizarea fără să ne avertizeze.

Răzvan

MULTUMESC

Daca lucrezi cu SQL 2008 te avantajeaza "MERGE". De exemplu:

MERGE INTO B AS trg
USING A AS src
ON src.id = trg.id
WHEN MATCHED THEN UPDATE SET trg.y = src.x
WHEN NOT MATCHED THEN INSERT (id, y) VALUES(src.id, src.x);

"MERGE" este inclusa in ANSI-SQL si te va avertiza in cazul unor probleme ca aceea mentionata de Razvan.