>>>>>>>>>>>> Luan O'Carroll wrote (2007-05-28 09:18:22): > The SQL given is for SQLServer and I can't find the correct SQL for > Derby, the problem being that Derby doesn't support the FROM clause in > an UPDATE statement. > > I've tried the following: > > UPDATE TEMP_RATES > SET TEMP_RATES.Level1=RATES.Level1 > WHERE TEMP_RATES.Level1 IN > SELECT ( > TEMP_RATES.Level1 > FROM TEMP_RATES > WHERE (TEMP_RATES.CODENO= RATES.CODENO) AND > (RATES.UserID=306) AND ( TEMP_RATES.BusinessID=1)) > > but then I get the error saying that "Column 'Level1' is either not in any > table in the FROM list or appears within a join specification and is > outside the scope of the join specification or appears in a HAVING clause > and is not in the GROUP BY list."
1) The statement is syntactically wrong, do you mean the following?
UPDATE TEMP_RATES
SET TEMP_RATES.Level1=RATES.Level1
WHERE TEMP_RATES.Level1 IN
(SELECT
TEMP_RATES.Level1
FROM TEMP_RATES
WHERE (TEMP_RATES.CODENO= RATES.CODENO) AND
(RATES.UserID=306) AND ( TEMP_RATES.BusinessID=1))
2) The table RATES is not a part of the query. I get the correct error
message (for my version of the statement):
ERROR 42X04: Column 'RATES.CODENO' is either not in any table in the FROM list
or appears within a join specification and is outside the scope of the join
specification or appears in a HAVING clause and is not in the GROUP BY list. If
this is a CREATE or ALTER TABLE statement then 'RATES.CODENO' is not a column
in the target table.
Do you intend to express something like the following (equi-join of
TEMP_RATES and RATES on CODENO)?
UPDATE TEMP_RATES
SET TEMP_RATES.Level1=RATES.Level1
WHERE TEMP_RATES.Level1 IN
(SELECT
TEMP_RATES.Level1
FROM TEMP_RATES, RATES
WHERE (TEMP_RATES.CODENO= RATES.CODENO) AND
(RATES.UserID=306) AND ( TEMP_RATES.BusinessID=1))
>
>
>
> [EMAIL PROTECTED] wrote:
>
> >Luan O'Carroll <[EMAIL PROTECTED]> writes:
> >
> >
> >
> >>I have a SQL Server query that I am trying to port to Derby but I
> >>can't find the right syntax. Is there any documentation?
> >>
> >>The SQLServer query is:
> >>
> >>UPDATE TEMP_RATES
> >>SET Level1=RATES.Level1
> >>FROM TEMP_RATES INNER JOIN RATES ON TEMP_RATES .CODENO= RATES .CODENO
> >>WHERE (RATES.UserID=306) AND ( TEMP_RATES.BusinessID=1)
> >>
> >>Any ideas. I tried the SQL for ANSI,Informix,Oracle but none worked
> >>
> >>
> >
> >What is the error message you are seeing? Which part of the query is
> >Derby choking on?
> >
> >
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
pgp2625MWwPYg.pgp
Description: PGP signature
