SQL 2000 UPDATE statements vs SQL 7

#1

Hello,

We've migrated from SQL 7 to SQL 2000 and it seems that some of the
existing UPDATE statements aren't working correctly.

-- current -- (SQL 7)
update company_items_quarterly
set company_id = c.company_id
from company_items_quarterly cq, company c
where cq.ticker = c.ticker
---------------

-- Modified -- (SQL 2000)
update cq
set cq.company_id = c.company_id
from company_items_quarterly cq, company c
where cq.ticker = c.ticker
---------------

will any of these statements work on SQL 7 and SQL 2000? It seems to
me that some procedures that use the UPDATE w/ JOIN work, and some
don't.

tnx in advance!

#2

Hi

This is actually mentioned in Books online as a level 4 compatibility issue
between 6.5 and higher releases.

I have found occassions where SQL7 does not complain regarding syntax, when
you would not expect the statement to work.

John

"Joca" <jjone99 (AT) hotmail (DOT) com> wrote

Quote:

Hello,

We've migrated from SQL 7 to SQL 2000 and it seems that some of the
existing UPDATE statements aren't working correctly.

-- current -- (SQL 7)
update company_items_quarterly
set company_id = c.company_id
from company_items_quarterly cq, company c
where cq.ticker = c.ticker
---------------

-- Modified -- (SQL 2000)
update cq
set cq.company_id = c.company_id
from company_items_quarterly cq, company c
where cq.ticker = c.ticker
---------------

will any of these statements work on SQL 7 and SQL 2000? It seems to
me that some procedures that use the UPDATE w/ JOIN work, and some
don't.

tnx in advance!

#3

Hi, and tnx all for the replies...

I don't get any errors when the alias is not used. I get NULL data, so
i've modified all the UPDATES that have JOINs and I'm hoping that this
will fix the NULL data. I just need some sort of explanation to give
to my client. The update statements were already written and working
on SQL 7. After the migration, they don't work anymore

tnx!

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Quote:

It is always a good idea to alias the tables and prefix the columns with the
appropriate alias so as there is no mistaking what they are supposed to be.
YOur using an alias for the FROM but not in the UPDATE part. You might also
get in the habit of using the ansi syntax for joins by using the ON clause
instead of the WHERE condition and stating the JOIN syntax as well. But
for Updates ina JOIN try this:

update cq set company_id = c.company_id
from company_items_quarterly cq INNER JOIN company c
ON cq.ticker = c.ticker

You state that it wasn't working correctly, were you getting an error?

--

Andrew J. Kelly
SQL Server MVP

"Joca" <jjone99 (AT) hotmail (DOT) com> wrote in message
news:cfe6019d.0307131123.58d6f434 (AT) posting (DOT) google.com...
Hello,

We've migrated from SQL 7 to SQL 2000 and it seems that some of the
existing UPDATE statements aren't working correctly.

-- current -- (SQL 7)
update company_items_quarterly
set company_id = c.company_id
from company_items_quarterly cq, company c
where cq.ticker = c.ticker
---------------

-- Modified -- (SQL 2000)
update cq
set cq.company_id = c.company_id
from company_items_quarterly cq, company c
where cq.ticker = c.ticker
---------------

will any of these statements work on SQL 7 and SQL 2000? It seems to
me that some procedures that use the UPDATE w/ JOIN work, and some
don't.

tnx in advance!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL 2000 UPDATE statements vs SQL 7

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

SQL 2000 UPDATE statements vs SQL 7 - 07-13-2003 , 02:23 PM

Re: SQL 2000 UPDATE statements vs SQL 7 - 07-13-2003 , 04:48 PM

Re: SQL 2000 UPDATE statements vs SQL 7 - 07-14-2003 , 12:54 PM