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 ).
