db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: What is the UPDATE syntax for using two tables?
Date Tue, 29 May 2007 11:56:45 GMT
>>>>>>>>>>>> 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))


> 
> 
> 
> Dyre.Tjeldvoll@Sun.COM wrote:
> 
> >Luan O'Carroll <luano@xoetrope.com> 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

Mime
View raw message