T-SQL 2005 UPSERT help

Question

T-SQL 2005 UPSERT help

Viewing 8 posts - 16 through 22 (of 22 total)

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

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 1

James.N (4/21/2009)
Nope, the above loig in not working either . It giving an error Msg 4104, Level 16, State 1, Line 1
"UPDATE TestD
SET TestD.cola=testS.col1,
TestD.colb=testS.col2
FROM TestD
WHERE EXISTS(SELECT * FROM testS WHERE tesS.KEY1=TestD.key1)
"

If you want to refference a table in your UPDATE clause, it must be specified in your FROM clause.

You are getting this error because the table testS does not exist in your UPDATE query.

You need to do something like this. (This is just an example, but not tested because you did not provide sample data.)

UPDATE TestD

SET TestD.cola=testS.col1

,TestD.colb=testS.col2

FROM TestD

JOIN testS ON tesS.KEY1=TestD.key1

James.N SSC Enthusiast Points: 193 More actions · Answer 2

James.N

SSC Enthusiast

Points: 193

April 21, 2009 at 3:34 pm

#981840

Thanks for all your time.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 3

James.N (4/21/2009)
I already have a upsert code in Test with Inner join, then deleting the common records and then insert,
and this code is working fine with out any issues.
UPDATE D
SET cola=S.col1,
colb=S.col2
FROM TestD D
INNER JOIN TestS S ON D.key1=S.KEY1
DELETE TestS
FROM TestS AS TS
INNER JOIN TestD AS TD ON TD.key1=TS.KEY1
INSERT INTO TESTD
SELECT * FROM TestS
The above logic is working fine with no issues
All I am trying to do is , I am trying to write the same Update code with INNER JOIN using EXISTS.
Just to check performance difference B/W innerjoin and EXISTS.
Thanks for all your time.

I don't understand what you are trying to accomplish. An INNER JOIN is going to be equivalent to a WHERE IN() or WHERE EXISTS()

In this case, I'm not sure that you can use EXISTS at all.

Here's a code example for using IN.

UPDATE D

SET cola=S.col1,

colb=S.col2

FROM TestD D

WHERE D.key1 IN

(

SELECT S.Key1

FROM TestS

)

//Edit: I hit submit accidentally.

James.N SSC Enthusiast Points: 193 More actions · Answer 4

HI,

Waht am I trying to accomplish , with the code I posted ?

First I am updating the Rows that are common in both TestD(Destination) and Test S(Staging)

Then, I am deleting all rows from TESTS that are EXIST in TestD or that are updated in TeasD in prevoius step.

Then, Inserting all the remainig rows from Test S to TestD.

Thanks for all your inputs.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 5

James.N (4/21/2009)
HI,
Waht am I trying to accomplish , with the code I posted ?
First I am updating the Rows that are common in both TestD(Destination) and Test S(Staging)
Then, I am deleting all rows from TESTS that are EXIST in TestD or that are updated in TeasD in prevoius step.
Then, Inserting all the remainig rows from Test S to TestD.
Thanks for all your inputs.

Yes, we understand that.

What I want to know is why you are looking to use EXISTS?

James.N SSC Enthusiast Points: 193 More actions · Answer 6

Hi,

Thanks for your reply.

Why I am trying to use EXISTS ? This is where things are getting interesting.

I was in Oracle team all these days and recently moved to new team where DB is SQL server .

I was told that, EXISTS works much faster on SQL server by big guys here at work , I have argued with them for hours to convience them with my logic but, no avail.Reson they gave me is things are not work in same way on Oracle and on SQL server :(.

Paul White SSC Guru Points: 150467 More actions · Answer 7

James.N (4/22/2009)
...I was told that, EXISTS works much faster on SQL server by big guys here at work

James,

Several of us have already posted example code that does what you ask, using EXISTS. So I'm a little confused about what else it is you are looking for.

EXISTS is a logical operation - the query optimizer has a wide range of choices about how to build a plan to physically accomplish it. For example, it is true that EXISTS is logically more efficient since we can stop looking for rows once a single row matches the predicate. This is called a semi-join. With an 'ordinary' JOIN, all rows must be examined, logically speaking. This may be the difference that the 'big guys' are referring to.

However, and without being unfair to them, I think it would be accepted by most experienced SQL Server people that EXISTS (and it's cousins INTERSECT and EXCEPT - which also usually result in a semi-join) is not always faster, nor is it even never slower - it depends on a wide variety of factors which are juggled by the optimizer to produce a reasonable physical access plan in reasonable time.

The optimizer is often smart enough to use either a join or a semi join to implement the EXISTS, but it is not a great idea to depend on this by trying to write every query with EXISTS. In many circumstances, it's just not possible to replace the logical JOIN with EXISTS statement(s) anyway.

For further information on some of the things I have mentioned see Craig Freeman's excellent introduction to joins and semi-join transformation.

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

James.N SSC Enthusiast Points: 193 More actions · Answer 8

James.N

SSC Enthusiast

Points: 193

April 22, 2009 at 4:54 pm

#982602

Thanks, Paul