CODEWITHSUNDEEP
sqlsql-server
user19224926
user19224926

Reputation:

Error in SQL UPDATE statement with WHERE EXISTS

I'm trying to write a query that updates a MaDeTai only if the MaSV exist in same table SinhVien in another server has linked. I want to set MadeTai = 'DT3' where MaSV = 'SVCNTT1' but when I run, SQL updates all of the table SinhVien, not just the MaSV = 'SVCNTT1'

UPDATE [DESKTOP-DPTRB14\MSSQLSERVER04].[DOANHUNRE].[dbo].[SinhVien]
SET MaDeTai = 'DT3' 
WHERE EXISTS (
    SELECT 1
    FROM [DESKTOP-DPTRB14\MSSQLSERVER01].[DOANHUNRE].[dbo].[SinhVien] SV2
    WHERE SV2.MaSV = MaSV AND SV2.MaSV = 'SVCNTT1'
);

Upvotes: 0

Views: 63

Answers (1)

Dale K
Dale K

Reputation: 27226

You're not correlating your exists sub-query with the table you are updating, so yes, its true for every row. Try the following:

UPDATE SV4 SET
    MaDeTai = 'DT3' 
FROM [DESKTOP-DPTRB14\MSSQLSERVER04].[DOANHUNRE].[dbo].[SinhVien] SV4
WHERE EXISTS (
    SELECT 1
    FROM [DESKTOP-DPTRB14\MSSQLSERVER01].[DOANHUNRE].[dbo].[SinhVien] SV1
    -- Correlate between the 2 tables
    WHERE SV1.MaSV = SV4.MaSV
)
AND SV4.MaSV = 'SVCNTT1';

Note the short, meaningful table aliases.

Upvotes: 1

Related Questions