Error in SQL UPDATE statement with WHERE EXISTS-CodePudding

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'
);

CodePudding user response：

You're not collelating 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.