a SQL statement with a large number of comparisons in WHERE...

Question

a SQL statement with a large number of comparisons in WHERE...

Christopher Klein

SSCrazy

Points: 2648
More actions
February 27, 2009 at 12:51 pm

#200828

Hi there,
I'm trying to see if there is a 'shorter' way to do a SQL statement...
UPDATE dbo.SomeTableName
SET COL1 = M.COL1,
COL2 = M.COL2,
COL3 = M.COL3,
...
...
...
COL40 = M.COL40,
COL41 = M.COL41
FROM @TBLMASTERS M
INNER JOIN dbo.SomeTableName T
ON M.ID = T.ID
WHERE
COL1 <> M.COL1 OR
COL2 <> M.COL2 OR
COL3 <> M.COL3 OR
...
...
...
COL40 <> M.COL40 OR
COL41 <> M.COL41
Is there any way to do the comparison without all of that?
thanks,
Chris

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 1

Your query does not look like T-SQL, so I don't know if this will work in your product. In T-SQL we could use the EXCEPT operator, as in:

SELECT {column list} FROM tablea

EXCEPT SELECT {column list} FROM tableb;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Christopher Klein SSCrazy Points: 2648 More actions · Answer 2

sorry, I thought it was pretty universal that ... means too much stuff to bother typing out

plus I fixed a typo so it reads UPDATE instead of PDATE. It is very much T-SQL though.

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 3

Okay - wasn't sure, but since your are on SQL Server the EXCEPT operator is available and can be used.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Christopher...

Let's think about this... what harm would it cause if all the items were equal and we still did the update anyway? Unless you have triggers or a TIMESTAMP datatype column on the table, there is no harm. If you have neither of those items, just let the update rip.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christopher Klein SSCrazy Points: 2648 More actions · Answer 5

Thanks for the replies. I'll see if we can just get rid of it all.