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