asked on

MS SQL -Update from another table - what is the fastes method?

I have 4 fields in 2 different tables that have to be updated regularly from a 3rd table
Below is what I am currently doing, but it is very slow.

--update PRDs
UPDATE dbo.PRDs SET RN = PVN.RN FROM
(SELECT RN, VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRDs.VN = PVN.VN
UPDATE dbo.PRDs SET EmpKey = PVN.EK FROM
(SELECT EK, VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRDs.VN = PVN.VN
UPDATE dbo.PRDs SET VCID = PVN.VN FROM
(SELECT VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRDs.VN = PVN.VN
UPDATE dbo.PRDs SET vNum = PVN.vNum FROM
(SELECT vNum, VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRDs.VN = PVN.VN

--update PRTs
UPDATE dbo.PRTs SET RN = PVN.RN FROM
(SELECT RN, VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRTs.VN = PVN.VN
UPDATE dbo.PRTs SET EmpKey = PVN.EK FROM
(SELECT EK, VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRTs.VN = PVN.VN
UPDATE dbo.PRTs SET VCID = PVN.VN FROM
(SELECT VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRTs.VN = PVN.VN
UPDATE dbo.PRTs SET vNum = PVN.vNum FROM
(SELECT vNum, VN FROM dbo.PV WHERE RN = @RN) AS PVN
WHERE dbo.PRTs.VN = PVN.VN

Is there a faster way to preform this update?

auke_t

Hi,

You should not update if the field contains the right value

--update PRDs
UPDATE  dbo.PRDs
SET     RN = PVN.RN
FROM    (SELECT RN,
                VN
         FROM   dbo.PV
         WHERE  RN = @RN
        ) AS PVN
WHERE   dbo.PRDs.VN = PVN.VN
        AND dbo.PRDs.RN <> PVN.RN

UPDATE  dbo.PRDs
SET     EmpKey = PVN.EK
FROM    (SELECT EK,
                VN
         FROM   dbo.PV
         WHERE  RN = @RN
        ) AS PVN
WHERE   dbo.PRDs.VN = PVN.VN
        AND dbo.PRDs.EmpKey = PVN.EK


And so on

Open in new window

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vdr1620

Try using a Join instead of a sub query.. below link has a good illustration

http://www.bennadel.com/blog/938-Using-A-SQL-JOIN-In-A-SQL-UPDATE-Statement-Thanks-John-Eric-.htm

Rajkumar Gs

Here is single query for your 4 separate queries for update PRDs

Backup your table and test it

Raj

UPDATE PRD
	SET RN = PV.RN, 
		EmpKey = PV.EK, 
		VCID = PV.VN,	
		vNum = PV.vNum
FROM dbo.PRDs PRD
	INNER JOIN dbo.PV ON PRD.VN = PV.VN 
WHERE PV.RN = @RN

Open in new window

Rajkumar Gs

Second one here

Raj

UPDATE PRT
	SET RN = PV.RN, 
		EmpKey = PV.EK, 
		VCID = PV.VN,	
		vNum = PV.vNum
FROM dbo.PRTs PRT
	INNER JOIN dbo.PV ON PRD.VN = PV.VN 
WHERE PV.RN = @RN

Open in new window

n2dweb

ASKER

Is there a performance difference between:

UPDATE R SET
RN = V.RN,
EmpKey = V.EK,
VCID = V.VN,
vNum = V.vNum
FROM dbo.PRDs R
inner join dbo.PV V ON R.VN = V.VN
WHERE V.RN = @RN

or
UPDATE dbo.PRD SET
RN = PV.RN,
EmpKey = PV.EK,
VCID = PV.VN,
vNum = PV.vNum
FROM dbo.PRDs PRD
INNER JOIN dbo.PV ON PRD.VN = PV.VN
WHERE PV.RN = @RN

cyberkiwi

No there isn't, but it is a good idea to alias the tables.
If you get into a habit, you will never trip up if you had to use the table multiple times including in multi-level queries.