Oracle guy with SQL Server question.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Oracle guy with SQL Server question.

Author

Topic

powellbound
Starting Member

23 Posts

Posted - 2007-03-10 : 22:57:12

Long time Oracle guy here working on a SQL Server gig and have a issue referencing multiple tables in updates and deletes.

In Oracle, I can do the following that is failing in SQLServer 2000.

update PS_REQ_HDR a set REQ_STATUS = 'C' where BUSINESS_UNIT = 'FORDH' and REQ_ID = '0000000265'
and not exists (select 'x' from PS_REQ_LINE b where a.BUSINESS_UNIT = b.BUSINESS_UNIT and a.REQ_ID = b.REQ_ID and b.CURR_STATUS <> 'C')

How would I do this in SQLServer?

TIA

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2007-03-10 : 23:09:30

maybe something like:


update a
set req_status = 'c'
from PS_REQ_HDR a
join PS_REQ_LINE b on a.BUSINESS_UNIT = b.BUSINESS_UNIT and a.REQ_ID = b.REQ_ID
where b.CURR_STATUS <> 'C' and a.BUSINESS_UNIT = 'FORDH' and a.REQ_ID = '0000000265'

Kristen
Test

22859 Posts

Posted - 2007-03-11 : 01:19:25

I think you only need to move the
UPDATE MyTable
bit to become
UPDATE MyAlias
SET ...
FROM MyTable AS MyAlias

e.g.


update a 
set REQ_STATUS = 'C' 
FROM PS_REQ_HDR a
where     BUSINESS_UNIT = 'FORDH' 
      and REQ_ID = '0000000265' 
      and not exists
      (
          select 'x'
          select 1 -- Convention is to use 1 for EXISTS statements, but the optimisers may not care! 
          from   PS_REQ_LINE b 
          where       a.BUSINESS_UNIT = b.BUSINESS_UNIT 
                  and a.REQ_ID = b.REQ_ID 
                  and b.CURR_STATUS <> 'C'
      )

You could use a JOIN but if the relationship is one PS_REQ_HDR : many PS_REQ_LINE it may be less efficient than using EXISTS

Kristen

powellbound
Starting Member

23 Posts

Posted - 2007-03-11 : 09:47:58

Kristen,

Thanks so much. What would the syntax look like using a join?

Dave

Kristen
Test

22859 Posts

Posted - 2007-03-12 : 08:34:28

"What would the syntax look like using a join?"

As "ehorn" posted.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources