Updating data in one table but has an inner join with another table

Question

Updating data in one table but has an inner join with another table

Viewing 4 posts - 16 through 18 (of 18 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Roy Ernest (2/5/2009)
This is the first time I am hearing that the SSMS changes the syntax of a query.
Yes, there was a mistake on the script. I had two ON in the JOIN part.
Update WLI
set WLI.linPartNum = replace(WLI.linPartNum, 'PL', 'ST')
from webOaLineItems as WLI
INNER JOIN webOaHeader as wH
ON WLI.linHeaId = wH.heaId
where (wH.heaCompletedDate IS NULL)
AND (WLI.linPartNum LIKE 'PL%')
If you run this, it should not change the syntax in any way. I am lost here. Sorry

If you use the graphical query editor in SSMS. it will try to "help" you with building your query, so things change if it doesn't "like" how you're writing something.

Let me ask this - what is WebOALineItems? Is that a table or a view? Is that column a calculation or a "true" column? Is there a "distinct" or a group by somewhere in underlying queries?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

Ah... That explains why it is changing. new info for me Matt. Thanks. No wonder I like hanging in this forum. Learn new thing everyday.

-Roy

navpreet.khurmi SSC Eights! Points: 933 More actions · Answer 3

Let me ask this - what is WebOALineItems? Is that a table or a view? - TABLE Is that column a calculation or a "true" column? - TRUE COLUMN Is there a "distinct" or a group by somewhere in underlying queries? - NO

NotManyPoints SSC Eights! Points: 808 More actions · Answer 4

Other than using the query design editor, never use it myself. I've seen a few people work this way and (well they get 'advised' not to) they open table and hit 'stop retrieving data' and use the SQL pane. Not saying this is what you are doing but its common.

They both try to 'help' with your syntax, the query gets 'helped' and fails.

Use 'New query' and the query will work fine (well the syntax anyway).

update xxx

set xxx.somefield = replace(xxx.somefield, 'A', 'B')

from sometable as xxx

inner join someothertable as yyy on xxx.field = yyy.field

where (xxx.somefield like 'A%')

But make sure you know your data as the replace may update more than just the rows which start with your like clause.