db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Luan O'Carroll <lu...@xoetrope.com>
Subject Re: What is the UPDATE syntax for using two tables?
Date Mon, 28 May 2007 08:18:22 GMT
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."



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?
>  
>

Mime
View raw message