SQL 7.0 Not allowing this update/join that worked in 6.5

SQL 7.0 Not allowing this update/join that worked in 6.5

Post by Bill Engel » Thu, 11 Mar 1999 04:00:00



We have an update statements similiar to this in several stored
procedures:

Update  FU
set ....
from FU a, BAR b
where a.colname = b.colname

These procedures now bomb with the odbc error:
Cannot use the column prefix 'a'.  This must match the object in the
UPDATE clause 'FU'.

Will 7.0 not allow this style update/join or is there a switch that can
be turned on to allow these ?

Thanks for any help,
Bill

 
 
 

SQL 7.0 Not allowing this update/join that worked in 6.5

Post by Dwayne Lanclo » Fri, 12 Mar 1999 04:00:00


Sounds like an ANSI compatibility thing.  You might want to consider setting
the database to 6.5 compatibility mode.

>We have an update statements similiar to this in several stored
>procedures:

>Update  FU
>set ....
>from FU a, BAR b
>where a.colname = b.colname

>These procedures now bomb with the odbc error:
>Cannot use the column prefix 'a'.  This must match the object in the
>UPDATE clause 'FU'.

>Will 7.0 not allow this style update/join or is there a switch that can
>be turned on to allow these ?

>Thanks for any help,
>Bill


 
 
 

SQL 7.0 Not allowing this update/join that worked in 6.5

Post by BPMargoli » Fri, 19 Mar 1999 04:00:00


You don't show the SET clause, but if the SET clause is something like:

SET a.column1 = ...,
         a.column2 = ...

You will get the message you describe. All you need to do is remove the a.
prefix and it will work.

As an explanation, you can only update a single table in an UPDATE
statement, namely the table listed after the keyword UPDATE. Therefore the
columns listed in the SET clause MUST of necessity belong to that table. The
prefix is therefore unnecessary.

 
 
 

1. SQL 7.0 Random function, does not work like SQL 6.5 Random function

We use the rand() function in our application login routine against a 6.5
database, in testing our migration to 7.0, we have found out the random
function in 7.0 does not behave exactly the same as in 6.5, trying the
following query from iSql against both a 6.5 database and a 7.0 database
returns different values - this behaviour "breaks" our application. Does
anyone else have any expierence using the random function?? Could you
provide some insight??




 BEGIN

  SET NOCOUNT ON

  SET NOCOUNT OFF
 END

        Tom Boyce
        Enterprise Project Manager
        National Institutes of Health, Center for Information Technology
        301-594-2666

2. Any one solve "NR" problem in BTRMON?

3. SQL-92 outer join vs T-SQL outer join (6.5 or 7.0) - test script included

4. Need Database for Publishing A

5. SQL 7.0 newbie question - error 1046/1045 in 7.0 but not in 6.5

6. JDBC

7. Image Datatype Like not working in 7.0 but OK in 6.5

8. Mass Disk Storage and Informix

9. DROP COLUMN not working after upgrade from 6.5 to 7.0

10. SQL Server 7.0 Update - logins not working

11. Acc97 code works in 6.5, not in 7.0

12. Microsoft SQL isapi extension - 405 Method Not Allowed - POST queries are not allowed - OpenXML

13. link SQL 7.0 to 6.5 ran instcat, 6.5 server not found