August 4, 2010 at 6:00 am
Hi all,
Have looked at all the posts to do with this but none seem applicable
Get this error msg when I run the code
The multi-part identifier "mf.m9rewh" could not be bound.
If I remove the UPDATE and SET and replace with SELECT it all works fine.
Hoping someone can kick me in the right direction
Here is the code
--------- Start of Code -----------------------
UPDATE mitfac
SET mf.m9rewh = 'TEST'
FROM
mitfac AS mf
INNER JOIN
mitmas as mm
ON
(
mm.mmitno = mf.m9itno
AND
mm.mmitno = 'E6808000001999A'
AND
mf.m9rewh = 'L12'
AND
mm.mmitcl = 'ICB'
)
---------------- End of Code --------------------------------------
August 4, 2010 at 9:18 pm
You can't use the table alias in the SET Statement. You aren't updating the column in MF, you are updating the column in mitfac. There are a couple of ways to do this. Here's the easy non-ANSI standard way:
UPDATE mf
SET m9rewh = 'TEST'
FROM
mitfac AS mf INNER JOIN
mitmas as mm
ON (
mm.mmitno = mf.m9itno AND
mm.mmitno = 'E6808000001999A' AND
mf.m9rewh = 'L12' AND
mm.mmitcl = 'ICB'
)
And here's an ANSI standard way:
UPDATE mitfac
SET m9rewh = 'TEST'
WHERE
EXISTS ( SELECT
1
FROM
mitmas as mm
WHERE
mm.mmitno = mitfac.m9itno AND
mm.mmitno = 'E6808000001999A' AND
mm.mmitcl = 'ICB' ) AND
mf.m9rewh = 'L12'
Neither of these solutions was tested, so verify they do what you want.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2010 at 2:18 am
Thanks for that Jack.
I did get it to work by using
UPDATE mf
instead of the actual MITFAC table name.
I am doing some boning up now on how SQL actually parses the sql code as it builds the query.
Coming from a 'normal' 🙂 programming world where code is done one line after the one before SQL is a mindset difference.
The actual code is to be run against an AS400 DB2 database. This was in my SQL test backend and it worked, of course does not mean it will work on DB2.
Thanks for your input.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply