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 10:52:47 GMT

>>
>>    
>>
>>>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?
>>    
>>
>
>The FROM.... part of the statement is not part of the SQL standard, so
>it's no wonder neither Derby nor none of the mentioned DB's accepts
>it.
>  
>
>I think (but not quite sure, since the I'm not familiar with this
>construct) that what you are trying to achieve may be written with a
>correlated subquery like this:
>
>UPDATE TEMP_RATES
>SET Level1 = (SELECT RATES.Level1 FROM RATES
>              WHERE TEMP_RATES.CODENO = RATES.CODENO AND
>                    RATES.USERID=306)
>WHERE TEMP_RATES.BusinessID=1;
>
>  
>
Thanks, that more or less worked (I had to add an EXISTS(SELECT 
Ratese.Level1....) clause to the WHERE ).

Mime
View raw message