You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Luan O'Carroll <lu...@xoetrope.com> on 2007/05/24 20:28:07 UTC

What is the UPDATE syntax for using two tables?

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

Re: What is the UPDATE syntax for using two tables?

Posted by Luan O'Carroll <lu...@xoetrope.com>.
>>
>>    
>>
>>>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 ).

Re: What is the UPDATE syntax for using two tables?

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Dyre.Tjeldvoll@Sun.COM wrote (2007-05-25 09:29:23):
> Luan O'Carroll <lu...@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

Re: What is the UPDATE syntax for using two tables?

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Luan O'Carroll wrote (2007-05-28 09:18:22):
> 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."

1) The statement is syntactically wrong, do you mean 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))

2) The table RATES is not a part of the query. I get the correct error
   message (for my version of the statement):

ERROR 42X04: Column 'RATES.CODENO' 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. If this is a CREATE or ALTER TABLE  statement then 'RATES.CODENO' is not a column in the target table.

Do you intend to express something like the following (equi-join of
TEMP_RATES and RATES on CODENO)?

UPDATE TEMP_RATES
SET TEMP_RATES.Level1=RATES.Level1
WHERE TEMP_RATES.Level1 IN
 	 (SELECT 
 		TEMP_RATES.Level1
 		FROM TEMP_RATES, RATES
 		WHERE (TEMP_RATES.CODENO= RATES.CODENO) AND 
 		(RATES.UserID=306) AND ( TEMP_RATES.BusinessID=1))


> 
> 
> 
> Dyre.Tjeldvoll@Sun.COM wrote:
> 
> >Luan O'Carroll <lu...@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?
> > 
> >

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

Re: What is the UPDATE syntax for using two tables?

Posted by Luan O'Carroll <lu...@xoetrope.com>.
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 <lu...@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?
>  
>

Re: What is the UPDATE syntax for using two tables?

Posted by Dy...@Sun.COM.
Luan O'Carroll <lu...@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?

-- 
dt