Database

UPDATE
     TableName tmp
SET
     start_date = (SELECT start_date
                   FROM TableName
                   WHERE Emp_id = tmp.Emp_id AND
                         Ben_Code = 1)
WHERE
     Ben_Code <> 1

PIEBALDconsult wrote:

rescan the table/index

mrfalk wrote:

one production time fix and won't be running in production

PIEBALDconsult wrote:

So is this how the responder writes production code when it matters

PIEBALDconsult wrote:

Perfect practice makes perfect. Do it the right way every time.

PIEBALDconsult wrote:

I see too much bad SQL being written. It should be nipped in its bud.

PIEBALDconsult wrote:

Won't that rescan the table/index for each id?

Michael Potter wrote:

increasing the code complexity

Michael Potter wrote:

lower the lookups by a 5th

PIEBALDconsult wrote:

may think that the provided code is good for all situations.

PIEBALDconsult wrote:

If you're going to teach a man to fish, do it right. Sure, in a pinch you can use a shotgun, but you shouldn't be teaching newbies to fish with a shotgun.

PIEBALDconsult wrote:

If you're going to teach a man to fish, do it right

UPDATE BEN
SET Start_date=B.Start_date
FROM BEN A
INNER JOIN 
(
  SELECT EmpId
  , Start_date 
  FROM BEN 
  WHERE Ben_code=1
) B
ON A.EmpId=B.EmpID
WHERE A.Ben_code<>1 -- optional