Updating Columns of one table from another table

[karanShagu]

hi all
I am a new bie to SQL
I am trying to convert an ACCESS query to SQL but getting erros like "command not ended properly" or "missing keywords"

The table to be updated:-- positions
To be Updated from :-- Imp_Positions_n ew


the Query is

[CODE=sql]UPDATE IMP_POSITIONS_N EW INNER JOIN POSITIONS

ON IMP_POSITIONS_N EW.JOBCODE = POSITIONS.POSIT ION_CODE

SET

POSITIONS.CUSTO M_MINIMUM = IIf([IMP_POSITIONS_N EW].[Grade]='MBC',[IMP_POSITIONS_N EW].[MRR_LOW],[IMP_POSITIONS_N EW].[MIN_RT_ANNUAL]),

POSITIONS.CUSTO M_MIDPOINT = IIf([IMP_POSITIONS_N EW].[Grade]='MBC',[IMP_POSITIONS_N EW].[MRP],[IMP_POSITIONS_N EW].[MID_RT_ANNUAL]),

POSITIONS.CUSTO M_MAXIMUM = IIf([IMP_POSITIONS_N EW].[Grade]='MBC',[IMP_POSITIONS_N EW].[MRR_HIGH],[IMP_POSITIONS_N EW].[MAX_RT_ANNUAL]);[/CODE]


Here is what I am trying to do

[CODE=sql]UPDATE POSITIONS SET
POSITIONS.CUSTO M_MINIMUM = CASE
WHEN IMP_POSITIONS_N EW.Grade='MBC'
THEN IMP_POSITIONS_N EW.MRR_LOW
ELSE IMP_POSITIONS_N EW.MRR_LOW
END,

POSITIONS.CUSTO M_MIDPOINT = CASE
WHEN IMP_POSITIONS_N EW.Grade='MBC'
THEN IMP_POSITIONS_N EW.MRP
ELSE IMP_POSITIONS_N EW.MID_RT_ANNUA L
END,

POSITIONS.CUSTO M_MAXIMUM = CASE
WHEN IMP_POSITIONS_N EW.Grade='MBC'
THEN IMP_POSITIONS_N EW.MRR_HIGH
ELSE IMP_POSITIONS_N EW.MAX_RT_ANNUA L
END

from IMP_POSITIONS_N EW
WHERE POSITIONS.POSIT ION_CODE = IMP_POSITIONS_N EW.JOBCODE[/CODE]


I have to use CASE expression to convert this

Please guide me where I am wrong and If possible please provide me with the corect query

[mwasif]

Hi karanShagu,
Welcome to TSDN!

Kidnly use proper CODE tags when posting source code.

I am trying to convert an ACCESS query to SQL

Coverting to MySQL query or MS SQL server?

[karanShagu]

hi
sorry I am new to the forum and yet have to read the rules :)

I am converting it to mySQL query

[code green]

Bit confused which is which.
The first looks like Access and the second looks like MsSql.
The IF statement can be used in MySql.
In the first statement you are joining two tables on the condition

IMP_POSITIONS_NEW.JOBCODE = POSITIONS.POSITION_CODE

Whilst in the second you are updating on this condition.
If you are trying to port the first statement into MySql then simply
replace the square brackets with backticks `.
Although neither are really needed when there are no spaces in the field name

[karanShagu]

Actually the second query is what I am tring to do
It may be wrong also.
I tried a lot of things for three days

The first query is indeed MSACCESS query

and I have to rewrite it in SQL
I cannot use inner join in SQL with the case Statement
It is giving error.

So on hte basis of first query can you suggest me a way to write and update query in msSQL using the case statement to update multiple columns

[code green]

So on hte basis of first query can you suggest me a way to write and update query in msSQL using the case statement to update multiple columns

Ah. That is where my confusion has occured.
You have posted on the MYsql forum rather than MSsql.
The thread will probably be moved by a moderator.
But I'll see if I can find your solution

[code green]

I use all three databases and get my syntax mixed up.
Your query is very close try placing the JOIN after the CASE conditions.

UPDATE POSITIONS SET
POSITIONS.CUSTOM_MINIMUM = CASE
WHEN IMP_POSITIONS_NEW.Grade='MBC'
THEN IMP_POSITIONS_NEW.MRR_LOW
ELSE IMP_POSITIONS_NEW.MRR_LOW
END, 
POSITIONS.CUSTOM_MIDPOINT = CASE
WHEN IMP_POSITIONS_NEW.Grade='MBC'
THEN IMP_POSITIONS_NEW.MRP
ELSE IMP_POSITIONS_NEW.MID_RT_ANNUAL
END, 
POSITIONS.CUSTOM_MAXIMUM = CASE
WHEN IMP_POSITIONS_NEW.Grade='MBC'
THEN IMP_POSITIONS_NEW.MRR_HIGH
ELSE IMP_POSITIONS_NEW.MAX_RT_ANNUAL
END 
FROM IMP_POSITIONS_NEW
JOIN POSITIONS 
ON POSITIONS.POSITION_CODE = IMP_POSITIONS_NEW.JOBCODE

There are about three ways of writing an UPDATE JOIN in MS.
One way I can never get to work.
Another looks weird.
The above looks like my favourite way

[karanShagu]

hi

I tried with your suggestion.
But still I am getting the message " ora 00933 command not ended properly" (on msSQL) or "ora 00900 invalid SQL statement" (on Toad)

On toad it shows error at From

I am using oracle 10g on one machine and also tried oracle for windows NT to check if it is supported there. But same result
The error ora 00900 also occurs when the syntax is not supported in a version.

Dude can we do it wit hthe Decode statement ?
May be decode can do the intended
If yes please explain me its working.
I am doing google for it and searching for the good material.



Thanks And regards

Karan

[code green]

I didn't spot the FROM line. It is not require so remove it
Try to use industry standard best practice when writing code.
That is commands in upper-case and names in lower-case.
It is easier to read

ELSE imp_positions_new.max_rt_annual
END 
FROM imp_positions_new *!*!*!  remove this

And instead of JOIN POSITIONS it should read

JOIN imp_positions_new