what is the wrong with this update

Question

Post reply

what is the wrong with this update

vision59

SSCrazy

Points: 2601
More actions
July 25, 2007 at 8:37 pm

#118003

UPDATE studentmaster
   SET active = 0
FROM studentdetail
WHERE studentmaster .orderset_sys = studentdetail.orderset_sys
   AND order_text like '%continuation of class%'and class_ID= ' 400000'

Thanks in advance

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Probably space in studentmaster .orderset_sys

_____________
Code for TallyGenerator

steve smith-401573 SSChampion Points: 10889 More actions · Answer 2

If your display is challenging you visually, you could always copy your SQL into Word or equivalent editor, then show codes to highlight improper characters or empty spaces that don't belong. Even text editors that will show you the hexidecimal equivalents may help.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

There is a potential problem in this update... I've seen it lock up servers if parallism is spawned and the indexes are just right... if you need to do a join in an UPDATE, the target table MUST be included in the join (as with every joined update example in Books Online). You should also get into the habit of using table aliases on every column in a join even if the column names are unique... I didn't add them to order_text or class_ID because I have no way of knowing which table they're actually in just by looking at this code.

Also, you have a space in studentmaster .orderset_sys [Edit: I see Serqiy also pointed that out]

UPDATE studentmaster

SET active = 0

FROM studentmast sm

studentdetail sd

WHERE sm.orderset_sys = sd.orderset_sys

AND order_text like '%continuation of class%'

AND class_ID= ' 400000'

--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)

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 4

Hi,

I have a query use it its working...........

UPDATE SM SET active = 0

FROM studentmaster SM Join studentdetail SD On studentmaster .orderset_sys = studentdetail.orderset_sys WHERE

order_text like '%continuation of class%'and class_ID= ' 400000'

Thanks & Regrads

Amit Gupta

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Almost, Amit, but not quite. Your query will generate 2 errors:

Server: Msg 107, Level 16, State 3, Line 1

The column prefix 'studentmaster' does not match with a table name or alias name used in the query.

Server: Msg 107, Level 16, State 1, Line 1

The column prefix 'studentdetail' does not match with a table name or alias name used in the query.

You meant to write this...

UPDATE SM SET active = 0 
FROM studentmaster SM 
 INNER JOIN studentdetail SD ON SD.orderset_sys = SM.orderset_sys 
WHERE order_text LIKE '%continuation of class%' AND class_ID = ' 400000'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden