February 5, 2009 at 10:02 am
Hello,
I have a query like this
SELECT *
FROM webOaLineItems INNER JOIN
webOaHeader ON webOaLineItems.linHeaId = webOaHeader.heaId
WHERE (webOaHeader.heaCompletedDate IS NULL) AND (webOaLineItems.linPartNum LIKE 'PL%')
Now I want to update these results by replacing the PL with ST. What is the best way.
Kindly suggest.
Initially I was trying to do it even manually as there are very less results but the sql server does not allow to change the data with inner joins and says that it is "read only".
Thanks.
February 5, 2009 at 10:24 am
I did not understand properly. Which table are you updating and what columns?
-Roy
February 5, 2009 at 10:33 am
I do this kind of thing all the time.
Update a
set a.field = b.field
from tablea a
inner join tableb b
on a.field = b.field
where a.field like 'Whatever%'
I have never gotten a read only error, do you accidentally leave a nolock hint in the syntax?
February 5, 2009 at 10:34 am
Thanks Roy.
The tables weboalineitems and weboaheader have an inner join on webOaLineItems.linHeaId = webOaHeader.heaId
The column I want to update is in webOaLineItems table and the column name is linPartNum
February 5, 2009 at 10:40 am
Just like Sarah Wagner posted, you should try updating the Alias.
Try this and please give us a feed back.
Update WLI
set WLI.linPartNum = 'ST'
from webOaLineItems as WLI
INNER JOIN webOaHeader as wH
on ON WLI.linHeaId = wH.heaId
where (wH.heaCompletedDate IS NULL)
AND (WLI.linPartNum LIKE 'PL%')
-Roy
February 5, 2009 at 10:44 am
if you need to replace the PL with ST, you can use the function replace in the set.
so it would be
Update WLI
set WLI.linPartNum = replace(WLI.linPartNum, 'PL', 'ST')
from webOaLineItems as WLI
INNER JOIN webOaHeader as wH
on ON WLI.linHeaId = wH.heaId
where (wH.heaCompletedDate IS NULL)
AND (WLI.linPartNum LIKE 'PL%')
February 5, 2009 at 11:01 am
Thanks a lot ROY. You guys were a great help.
February 5, 2009 at 11:12 am
Glad to be of help
-Roy
February 5, 2009 at 11:16 am
Actually when I tried it.
UPDATE WLI
SET WLI.linPartNum = replace(WLI.linPartNum, 'ST', 'PL')
FROM webOaLineItems AS WLI INNER JOIN
webOaHeader AS wH ON ON WLI.linHeaId = wH.heaId
WHERE (wH.heaCompletedDate IS NULL) AND (WLI.linPartNum LIKE 'ST%')
The tool changes my query to
UPDATE WLI
SET WLI.linPartNum = REPLACE(WLI.linPartNum, 'ST', 'PL')
FROM webOaLineItems AS WLI INNER JOIN
webOaHeader AS wH ON WLI.linHeaId = wH.heaId CROSS JOIN
WLI
WHERE (wH.heaCompletedDate IS NULL) AND (WLI.linPartNum LIKE 'ST%')
and says that the field linpartnum cannot be updated.
February 5, 2009 at 11:20 am
What tool are you using?
Did you try running it in Sql Server management Studio?
-Roy
February 5, 2009 at 11:27 am
yes that's what I am using. SQL Server 2005 Management studio
February 5, 2009 at 11:32 am
yes sql server 2005 management studio
February 5, 2009 at 11:34 am
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
-Roy
February 5, 2009 at 11:39 am
Thanks again Roy but I am getting the same error.
when i run your query. The management studio comes up with an error.
Column or expression 'linPartNum' cannot be updated.
and also adds CROSS JOIN WLI in the query.
February 5, 2009 at 11:44 am
Question...
SELECT *
FROM webOaLineItems INNER JOIN
webOaHeader ON webOaLineItems.linHeaId = webOaHeader.heaId
WHERE (webOaHeader.heaCompletedDate IS NULL) AND (webOaLineItems.linPartNum LIKE 'PL%')
webOaLineItems.linHeaId = webOaHeader.heaId is it a one to one relationship?
-Roy
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply