Cannot use subquery in UPDATE statement?

Hi there,
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> >, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:
>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> > Hi there,
> >
> > I try to run the following update statement on MS SQL Server 2000 SP4
> >
> > UPDATE Site
> >
> > SET Active = 0
> >
> > WHERE
> > RID IN (SELECT site.RID AS site_rid
> > FROM Site INNER JOIN
> > ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> > Manager m ON ms.ManagerID = m.RID
> > WHERE (am.UserName = 'iwk1004') AND
> > (ms.isPrimaryUser = 1))
> >
> > Running this query I get the message "Subquery returned more than 1
> > value. This is not permitted when the subquery follows =, !=, <, <= ,
> >>, >= or when the subquery is used as an expression."
> >
> > Which is weird, because according to the documentation and examples I
> > inferred could use UPDATE with the use of IN in the condition and the
> > subquery returining multiple rows. (sub-query works fine BTW)
> >
> > Any idea what is going on?
> >
> > Regards,
> >
> > Iwan
> >
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ

Cannot use subquery in UPDATE statement?

Hi there,
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:

> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:

>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
[vbcol=seagreen]
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ

Cannot use GROUP BY clause the way I want to?

I'm trying to use a GROUP BY clause in my SQL statement when retieving information from an SQL Server DB. The only problem is that it won't let me 'SELECT' columns from the database that are not part of the GROUP BY clause. Here is my example:

This works:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA, ColumnB

This does NOT work:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA

It simply will not let me have ColumnB in the SELECT clause unless I put it in the GROUP BY clause. Is there any way around this? Because I need both columns to display in the page, but I only want to group them by one column.

I'm coming from MySQL, and in MySQL what I want to do is perfectly legal. However, in SQL Server it's not...

Any ideas?Let me try to answer your question.

Create a simple table first:
ColumnA, ColumnB
x, y
x, z

If you group it by ColumnA, it returns a single row of 'x'. What value of ColumnB you want diplay? 'y' or 'z'?

Each group of ColumnA may have multiple values for ColumnB. Now you know why SQL won't allow you have columnB in SELECT clause.

What you can do is to use functions like MIN(), MAX()..to choose the a single value from inside the group. Or aggregation fuctions if columnB is numeric:

SELECT ColumnA, Min(ColumnB) FROM MyTable GROUP BY ColumnA

It shows the minimum ColumnB value inside the group.|||Group by is not really useful unless you want to get some aggregate value such as SUM, AVG, MIN, etc.

What you can do is:

select a.ColA, a.ColB, b.Total
from Table1 a
join
(select ColA, Sum(ColC) as Total
from Table1
group by ColA) b
 on a.ColA = b.ColA

This will give you what I think you want. You'll have all the values of ColA and ColB with the total based on ColA alone. Is DISTINCT what you're really after?|||I was having a similar problem, and DISTINCT worked for me:

"SELECT DISTINCT tblRequest.MgmntID, tblMgmnt.MgrName FROM tblRequest"
I had been trying GROUP BY, UNIQUE, but couldn't get it to work.
Thanks pdraigh
SMC

Cannot Upgrade Stored Procedures to SQL 2000

I have a SQL 6.5 that containts the statement "RAISERROR
(@.@.ERROR,16,1)" in a lot of stored procedures. When I try
creating those procedures on SQL 2000 I get a syntax error
pointing to the statement. Does someone knows any database
option or something like that that I could change to avoid
that error?
Check out sp_dbcmptlevel in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Reinaldo Sture" <reinaldo.sture@.barcap.com> wrote in message
news:39b201c4aa52$de22f690$a401280a@.phx.gbl...
I have a SQL 6.5 that containts the statement "RAISERROR
(@.@.ERROR,16,1)" in a lot of stored procedures. When I try
creating those procedures on SQL 2000 I get a syntax error
pointing to the statement. Does someone knows any database
option or something like that that I could change to avoid
that error?
|||Compatibility model is 8.0, but back it is my last option... Are there
something else that I can do?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
Thomson
Hi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:

> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>
|||identity columns are not updateable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegro ups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
ThomsonHi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>|||identity columns are not updateable.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegroups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
ThomsonHi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:

> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>|||identity columns are not updateable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegroups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot update

Hi,
I have the following update statement but using a linked server
Update txq005dev1.sprint.dbo.becinfo set unit = a.unit from becinfo a inner
join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
a.ChargSubType = b.ChargSubType
I am not sure why it doesn't work. Can I not use Linked server in an Update
Statement?
Thanks
Ed
The following is an error message:
Server: Msg 7306, Level 16, State 2, Line 2
Could not open table '"sprint"."dbo"."becinfo"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...What product are you linking to?
ML|||Ed,
Does the LS table have a PK or UNIQUE constraint? If not, might try adding
one and then trying again.
HTH
Jerry
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:154D0BE7-5EDC-4897-A9BD-C7903ABF1165@.microsoft.com...
> Hi,
> I have the following update statement but using a linked server
> Update txq005dev1.sprint.dbo.becinfo set unit = a.unit from becinfo a
> inner
> join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
> a.ChargSubType = b.ChargSubType
> I am not sure why it doesn't work. Can I not use Linked server in an
> Update
> Statement?
> Thanks
> Ed
> The following is an error message:
> Server: Msg 7306, Level 16, State 2, Line 2
> Could not open table '"sprint"."dbo"."becinfo"' from OLE DB provider
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
> generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
>|||Try this
Update txq005dev1.sprint.dbo.becinfo set unit = (SELECT unit from becinfo a
inner join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
a.ChargSubType = b.ChargSubType)
if you are sure that inner query results only one value
--
Regards
R.D
--Knowledge gets doubled when shared
"Jerry Spivey" wrote:

> Ed,
> Does the LS table have a PK or UNIQUE constraint? If not, might try addin
g
> one and then trying again.
> HTH
> Jerry
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:154D0BE7-5EDC-4897-A9BD-C7903ABF1165@.microsoft.com...
>
>

Cannot set a Variable from a select statement that contains a variable? Help Please

I am trying to set a vaiable from a select statement

DECLARE @.VALUE_KEEP NVARCHAR(120),

@.COLUMN_NAME NVARCHAR(120)

SET @.COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')

SET @.VALUE_KEEP = (SELECT @.COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)

PRINT @.VALUE_KEEP

PRINT @.COLUMN_NAME

RESULTS

-

FirstName <--@.VALUE_KEEP

FirstName <--@.COLUMN_NAME

SELECT @.COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent

How do I make this select statement work using the @.COLUMN_NAME variable?

Any help greatly appreciated!

Your second SET statement is just applying the value of @.COLUMN_NAME to @.VALUE_KEEP. Based on the hardcoding in the first set statement I'm not sure exactly what you would be achieving with the variable use. If you are just trying to select a column dynamically from a given table you would need to use dynamic sql. Syntax something like...

DECLARE @.myVariable varchar(50), @.sql nvarchar(max) --or 4000 if using SQL 2000

SET @.myVariable = 'FirstName'

SET @.sql = 'SELECT ' + @.myVariable + ' FROM dbo.myTable WHERE myColumn = myColumn'

EXEC sp_executesql @.sql

That said, I would be very wary of using this approach in an application as there are security and maintainability issues with dynamic sql.

|||

I am passing this to coalesce()

my goal is to dynamically loop through the columns using coalesce() that is part of my stored procedure for de-duplicating a database. Since the columns can change I wanted to pull the columns and table dynamically.

I compare the records of the duplicates to update any null fields and want to do something like

coalesce(@.Record_Being_Kept, @.Record_Being_Replaced)

so two things...

I want the values in the coalesce ('Brent', 'Brent) when it is comparing the firstnames "Obviously this is much more applicable with the phone and email info" to Merge the Dupes.

I don't know if there is a way to pass EXEC sp_executesql @.sql to coalesce() ?

Cannot schema bind function 'fn_xxxx' because it contains an EXECUTE statement

Does anyone have any pointers on this error message?
Funny thing is that no where in the SQL documentation does it say that an
EXEC is not allowed in a function with SCHEMABINDING option set.Dynamic SQL is not allowed within a function at all, schemabinding or not...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Clark" <steve3264@.hotmail.com> wrote in message
news:e8o#nMX4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> Does anyone have any pointers on this error message?
>
> Funny thing is that no where in the SQL documentation does it say that an
> EXEC is not allowed in a function with SCHEMABINDING option set.
>|||The rules for the allowable constructs in functions are not well documented.
This one is almost there though. EXEC is only permitted if it references an
extended proc (or another function - but that's pretty much redundant).
Extended procs reside in Master but schema binding requires that "All
objects referenced by the function must be in the same database as the
function". So as documented, you could only ever create functions containing
EXEC as schemabound if they were in Master... but in reality that doesn't
seem to work either - not that it would be very useful anyway... :)
As Aaron says, dynamic SQL isn't permitted in functions ever. Nor are calls
to regular SPs.
--
David Portas
SQL Server MVP
--

Cannot schema bind function 'fn_xxxx' because it contains an EXECUTE statement

Does anyone have any pointers on this error message?
Funny thing is that no where in the SQL documentation does it say that an
EXEC is not allowed in a function with SCHEMABINDING option set.
Dynamic SQL is not allowed within a function at all, schemabinding or not...
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Clark" <steve3264@.hotmail.com> wrote in message
news:e8o#nMX4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> Does anyone have any pointers on this error message?
>
> Funny thing is that no where in the SQL documentation does it say that an
> EXEC is not allowed in a function with SCHEMABINDING option set.
>
|||The rules for the allowable constructs in functions are not well documented.
This one is almost there though. EXEC is only permitted if it references an
extended proc (or another function - but that's pretty much redundant).
Extended procs reside in Master but schema binding requires that "All
objects referenced by the function must be in the same database as the
function". So as documented, you could only ever create functions containing
EXEC as schemabound if they were in Master... but in reality that doesn't
seem to work either - not that it would be very useful anyway...
As Aaron says, dynamic SQL isn't permitted in functions ever. Nor are calls
to regular SPs.
David Portas
SQL Server MVP