Updating using OUTER APPLY - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Updating using OUTER APPLY

Author

Topic

Roland
Starting Member

2 Posts

Posted - 2011-04-28 : 05:51:04

Please do you know what is wrong with this query:

Update SB SET SB.TypeCode = A.OCode from Stock_Biblio as SB
outer apply (Select OCode from O_BlurbMap where O_BlurbMap.BlurbID = SB.BlurbType and O_BlurbMap.CompanyRecNo = SB.CompanyRecNo) as A
where SB.StockCode = '00121' and SB.CompanyRecNo = 1 and SB.BlurbType IN (Select BlurbID from O_BlurbMap where O_BlurbMap.CompanyRecNo = SB.CompanyRecNo)

It gives an error when executed in sql server compact.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-28 : 06:48:30

could you do one of these?

Update SB
SET SB.TypeCode = (Select OCode from O_BlurbMap where O_BlurbMap.BlurbID = SB.BlurbType and O_BlurbMap.CompanyRecNo = SB.CompanyRecNo)
from Stock_Biblio as SB
where SB.StockCode = '00121' and SB.CompanyRecNo = 1
and SB.BlurbType IN (Select BlurbID from O_BlurbMap where O_BlurbMap.CompanyRecNo = SB.CompanyRecNo)

Update SB
SET SB.TypeCode = A.OCode
from Stock_Biblio as SB
join O_BlurbMap A
on A.BlurbID = SB.BlurbType and A.CompanyRecNo = SB.CompanyRecNo
where SB.StockCode = '00121' and SB.CompanyRecNo = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Roland
Starting Member

2 Posts

Posted - 2011-04-28 : 13:01:49

Thanks for the reply.
Neither of the 2 options worked for sql server compact 3.5.
They both display the same kind of errors.
I would actually prefer an UPDATE syntax using OUTER REPLY.

thanks

Roland

quote:
Originally posted by nigelrivett

could you do one of these?

Update SB
SET SB.TypeCode = (Select OCode from O_BlurbMap where O_BlurbMap.BlurbID = SB.BlurbType and O_BlurbMap.CompanyRecNo = SB.CompanyRecNo)
from Stock_Biblio as SB
where SB.StockCode = '00121' and SB.CompanyRecNo = 1
and SB.BlurbType IN (Select BlurbID from O_BlurbMap where O_BlurbMap.CompanyRecNo = SB.CompanyRecNo)

Update SB
SET SB.TypeCode = A.OCode
from Stock_Biblio as SB
join O_BlurbMap A
on A.BlurbID = SB.BlurbType and A.CompanyRecNo = SB.CompanyRecNo
where SB.StockCode = '00121' and SB.CompanyRecNo = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-28 : 13:17:23

What specific error messages are you getting? Also, if outer apply isn't the best solution (and I'm not saying that's the case), do you still need the outer apply solution?

Jim

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-28 : 13:46:54

How about these
Looks like a from clause isn't allowed in compact edition - probably apply isn't either.

Update SB
SET SB.TypeCode = (select A.OCode
from Stock_Biblio as SB
join O_BlurbMap A
on A.BlurbID = SB.BlurbType and A.CompanyRecNo = SB.CompanyRecNo
)
where exists (select *
from Stock_Biblio as SB
join O_BlurbMap A
on A.BlurbID = SB.BlurbType and A.CompanyRecNo = SB.CompanyRecNo
where SB.StockCode = '00121' and SB.CompanyRecNo = 1 )

Update SB
SET SB.TypeCode = (select A.OCode
from Stock_Biblio as SB
join O_BlurbMap A
on A.BlurbID = SB.BlurbType
and A.CompanyRecNo = SB.CompanyRecNo
)
where SB.StockCode = '00121' and SB.CompanyRecNo = 1
and SB.BlurbType IN (Select BlurbID from O_BlurbMap where O_BlurbMap.CompanyRecNo = SB.CompanyRecNo)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources