update with inner join problem

[mgriffith]

after reviewing the faq on updating with inner joins, i tried my own and failed miserably...anyone have any ideas on why this won't work (it's not giving me an error message when i try to add it, it just says operation not allowed)...it's sql 7.0

--------------------------------------------------
CREATE PROCEDURE sp_wiz_tblProcesses_UpdateByPercent
(
@Process_ID int,
@wc char(8),
@percentage decimal(9),

)
AS

UPDATE
tblRunCardProcessData AS DATA

--hook up workcenter stuff to grab only the processes we want
INNER JOIN
tblWCProcesses AS _WC
ON
DATA.WCProcess_ID = _WC.WCProcess_ID

SET
--set it to a percentage of it's original value, and then make it a varchar for storing
DATA.WCProcess_tol = convert(varchar, (
convert(decimal, WCProcess_tol) * (@percentage / 100) ))

WHERE
_WC.wc = @wc
AND
_WC.Process_ID = @Process_ID
AND

--don't include non-numeric...we'll have trouble updating
isnumeric(DATA.WCProcess_val)
----------------------------------------------------

thanks
mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[sunila7]

Hi,
Try this SP's
----Frist One
CREATE PROCEDURE sp_wiz_tblProcesses_UpdateByPercent

( @Process_ID int,
@wc char(8),
@percentage decimal(9)

)
AS

UPDATE
tblRunCardProcessData

SET

tblRunCardProcessData.WCProcess_tol = convert(varchar, (
convert(decimal, WCProcess_tol) * (@percentage / 100) ))


WHERE
tblRunCardProcessData.WCProcess_ID = tblWCProcesses.WCProcess_ID AND
WC.wc = @wc
AND
WC.Process_ID = @Process_ID
AND
isnumeric(tblRunCardProcessData.WCProcess_val)=1

OR

---Second One
CREATE PROCEDURE sp_wiz_tblProcesses_UpdateByPercent
(
@Process_ID int,
@wc char(8),
@percentage decimal(9)

)
AS

UPDATE
tblRunCardProcessData
--hook up workcenter stuff to grab only the processes we want

SET
--set it to a percentage of it's original value, and then make it a varchar for storing
tblRunCardProcessData.WCProcess_tol = convert(varchar, (
convert(decimal, WCProcess_tol) * (@percentage / 100) ))
FROM tblRunCardProcessData data
INNER JOIN
tblWCProcesses AS _WC
ON
DATA.WCProcess_ID = _WC.WCProcess_ID

WHERE
_WC.wc = @wc
AND
_WC.Process_ID = @Process_ID
AND

--don't include non-numeric...we'll have trouble updating
isnumeric(DATA.WCProcess_val) =1
----------------------------------------------------


Sunil

 

[mgriffith]

i realized that extra comma right away, and i took it out and have tried both of your suggestions and a few others to no avail...i've taken out all AS statements, and i'm left with (still not working):

---------------------------------------------
CREATE PROCEDURE sp_wiz_tblProcesses_UpdateByPercent
(
@Process_ID int,
@wc char(8),
@percentage decimal(9)

)
AS

UPDATE
tblRunCardProcessData

--hook up workcenter stuff to grab only the processes we want
INNER JOIN
tblWCProcesses
ON
tblRunCardProcessData.WCProcess_ID = tblWCProcesses.WCProcess_ID

SET
--set it to a percentage of it's original value, and then make it a varchar for storing
tblRunCardProcessData.WCProcess_tol = convert(varchar, (
convert(decimal, tblRunCardProcessData.WCProcess_tol) * (@percentage / 100) ))

WHERE
tblWCProcesses.wc = @wc
AND
tblWCProcesses.Process_ID = @Process_ID
AND
isnumeric(tblRunCardProcessData.WCProcess_val) --don't include non-numeric...we'll have trouble updating
---------------------------------------------

any other suggestions mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[SQLBill]

What exactly are you trying to do? It looks like you are trying to update two tables at once by using a join and looking through BOL under UPDATE it appears you can only update one table per UPDATE.

-SQLBill

 

[sunila7]

Hi,

I think SQL Server doesn't support the UPDATE statement SQL u r trying to use.

I think this FAQ faq183-1958, should be for access Databases.


Sunil

 

[SQLSister]

I agee with sunila, can't update two tables at the same time even if they are in a view!

 

[SQLBill]

By the way....I looked through BOL some more and from the first paragraph of JOIN FUNDAMENTALS:

"By using joins, you can retrieve data from two or more tables..."

Note the '... can RETRIEVE data FROM two or more tables'


-SQLBill

 

[mgriffith]

it's actually a statement that is called from an online wizard that updates tons of rows in the [tblRunCardProcessData] table if a change is made to the [tblProcesses] table which isn't shown....it's a low level thing that links into the [tblWCProcesses] by ID.

the table that is being updated (which contains individual processes for a given workcenter) is linked to the WCProcesses table (which contains groups of the former, without the individual details--in case we have a process that is done multiple times) by the id which then links to another table of Processes (which are the root operations)

what's happening is, if someone makes a tolerance for an operation required (in tblProcesses) then i need to update all of the RunCards (in tblRunCardProcessData) at a specified workcenter { or % for all workcenters (i hope that will work...i may have to change the = to a LIKE) } that use that process mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[mgriffith]

well shoot

so i won't be able to do this??? yikes!

i think i'll have to do some setting of variables from a couple of select statements and then use those variables in the later statements..i didn't want to do that...oh well that's life...it shouldn't be too bad i guess

thanks all for your help

-----------------------
update tblRunCardProcessData set wcprocess_tol = convert(...)
where
wcprocess_id = (select wcprocess_id from wcprocesses where wc = @wc and process_id = @process_id)
and
not isnull(wcprocess_val)
-------------------------

i'm going to try that there out mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[SQLBill]

Check out triggers in BOL. You can create a trigger on tblProcesses which will 'fire' when an update is made. Then in the trigger you have it update tblRunCardProcessData. The main purpose of triggers (as I see it) is to cause something to happen when something else happens. So, update table 2 when table 1 is updated is a function of a trigger.

-SQLBill

 

[mgriffith]

i thought about that, but i need user input before i can decide what to do to the data table.....this update by percent is one stored procedure, and it needs a value to update by (which is given by the user)...but they also have the option of running a different stored procedure that updates the data using =,+,-,/ and a specified value

i've tried these and neither work...it seems like they should though
-------------------------------------------
UPDATE
tblRunCardProcessData

SET
--set it to a percentage of it's original value, and then make it a varchar for storing
tblRunCardProcessData.WCProcess_tol = convert(varchar, (
convert(decimal, tblRunCardProcessData.WCProcess_tol) * (@percentage / 100) ))

WHERE
EXISTS (
SELECT
tblWCProcesses.WCProcess_ID
FROM
tblWCProcesses
WHERE
tblWCProcesses.WCProcess_ID = tblRunCardProcessData.WCProcess_ID
AND
tblWCProcesses.wc = @wc
AND
tblWCProcesses.Process_ID = @Process_ID
)
AND
NOT isnull(tblRunCardProcessData.WCProcess_val)
--------------------------------------
and
----------------------------------
UPDATE
tblRunCardProcessData

SET
--set it to a percentage of it's original value, and then make it a varchar for storing
tblRunCardProcessData.WCProcess_tol = convert(varchar, (
convert(decimal, tblRunCardProcessData.WCProcess_tol) * (@percentage / 100) ))

WHERE
tblRunCardProcessData.WCProcess_ID = (
SELECT
tblWCProcesses.WCProcess_ID
FROM
tblWCProcesses
WHERE
tblWCProcesses.wc = @wc
AND
tblWCProcesses.Process_ID = @Process_ID
)
AND
NOT isnull(tblRunCardProcessData.WCProcess_val)
-----------------------------
mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu