Database

Mycroft Holmes wrote:

because the where clause will take advantage of any indexes where the concatenated string will not

SELECT Details.*, 
       (SELECT Results.VE
        From Results r1
        WHERE Details.SN = r1.SerialNumber
        AND   ID = (SELECT MAX(ID) 
                    FROM Results r2
                    WHERE Details.SN = r1.SerialNumber)
FROM Details;

• in the statement, you have a reference to src, but it's not defined. Typo?
• I know that the source cannot have more than one row if update is used like this (or at least the results may be incorrect), but could it also be that the source row must have exactly one matching row. But then again, just guessing

CREATE TABLE Test1 (
Column1 varchar(50)
);

INSERT INTO Test1 (Column1)
VALUES ('ABC');

CREATE TABLE Test2 (
Column2 varchar(50)
);

UPDATE Test1
SET Test1.Column1 = Test2.Column2
FROM Test2

UPDATE Test1
SET Test1.Column1 = a.Column2
FROM Test2 a
INNER JOIN Test2 b
ON a.Column2 = b.Column2

UPDATE MyTable
SET p.Column1 = (SELECT pm.Column1
                 FROM Property p
                 inner join Address a
                 on p.PropertyRef = a.PropertyRef
                 inner join remotedb.PropertyMaster pm
                 on pm.PropertyRef = p.PropertyRef)