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 Fri, 25 May 2007 08:43:04 GMT
>>>>>>>>>>>> Dyre.Tjeldvoll@Sun.COM wrote (2007-05-25 09:29:23):
> 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?

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;


-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message