>>>>>>>>>>>> [EMAIL PROTECTED] wrote (2007-05-25 09:29:23): > 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?
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
pgpow4kVOnBYP.pgp
Description: PGP signature
