Multi-Table Update?

Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?

What is the alternatives??

I have to match two tables up and use records from one to update the other and creating a view isn't working...I would have suggested a view. What kind of problems are you getting with this approach? Are you trying to update a key value?|||
Another option is to use a stored procedure. You can update multiple tables, one after the other, using a stored procedure. Within the stored procedure you can use variables to hold values retrieved from one table and insert them into the next one.

|||you tend to lose update capabilities with a join when its a many to many join.

if your joining with a primary key it should work|||I'm going to have to learn some more about stored procedures because that would be excellent to do multiple updates like that..

Is there a code sample or link where I can learn more about doing the updates in that manor?

thx|||
Probably your best bet is to find a good book on the topic. In the meantime, you can hava look at the books online:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_4hk5.asp?frame=true

You can also download some of theStarter Kits from this website for lots of great samples (see the tab above).

Hope this helps.

MULTI-Table Update Queries

I'm new to adp w/ sql server but I have to use it on a project i'm
doing...
One of the MUSTS for this project is the ability to update a 00 - 09
text value with the appropriate text description from another table...
Easy as pie in .mdb. Of course In the stored procedure it barks at me
and tells me that an update query can only have one table.. ouch that
hurts...

I'm currently reading on the subject but this group has been very
helpful in the past....

I found this link...
http://www.sqlservercentral.com/col...stheeasyway.asp

Unfortunetly I'm using MSDE not Enterprise so I don't think I can use
the query analyser.. But I tryed it in my Access ADP anyway
it barked at me..

I tried to go from this...

SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1
FROM dbo.TEST INNER JOIN
dbo.LU_SEX ON dbo.TEST.SEX_DP1 =
dbo.LU_SEX.SEX_DEC

To this...

UPDATE dbo.TEST.SEX_DP1
SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODE
FROM dbo.LU_SEX INNER JOIN
dbo.TEST ON dbo.LU_SEX.SEX_DEC =
dbo.TEST.SEX_DP1

Maybe I need a good book on this?

Thanks,
CharlesIt looks like you wanted:

UPDATE dbo.TEST
SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODE
FROM dbo.LU_SEX
JOIN dbo.TEST
ON dbo.LU_SEX.SEX_DEC = dbo.TEST.SEX_DP1

The table name follows the UPDATE keyword, not the column name.

This is the MS proprietary syntax for an UPDATE statement. There is a
potential problem with that method if you try to update using a join
that doesn't return a unique value for each row. No error is returned
but the result is undefined and may be unreliable. This is one reason I
prefer the ANSI Standard UPDATE syntax, which SQL Server also supports:

UPDATE Test
SET sex_dp1 =
(SELECT sex_code
FROM LU_Sex
WHERE sex_dec = Test.sex_dp1)

I'm guessing this will be the equivalent of what you posted. It will be
so long as sex_dec is unique in LU_Sex and all the values of sex_dp1
also exist in that table.

Presumably you ultimately intend to use one set of codes througout the
database. There is in fact an International standard for gender codes
(0=not known 1=male 2=female 9=not specified). You could also use views
to convert the codes if you need to do so for display for example.

--
David Portas
SQL Server MVP
--|||(meyvn77@.yahoo.com) writes:
> I tried to go from this...
> SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1
> FROM dbo.TEST INNER JOIN
> dbo.LU_SEX ON dbo.TEST.SEX_DP1 =
> dbo.LU_SEX.SEX_DEC
> To this...
> UPDATE dbo.TEST.SEX_DP1

You have a database dbo with a schmea TEST and table SEX_DP1?
Drop SEX_DP1 to start with.

> SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODE

And here drop dbo.TEST on the left-hand side. You can uses prefixes on
the left-hand side, but I don't recall under which circumstances. In any
case, since you can only update one table at a time, it's redundant.

> FROM dbo.LU_SEX INNER JOIN
> dbo.TEST ON dbo.LU_SEX.SEX_DEC =
> dbo.TEST.SEX_DP1

This should be fine, although I recommend you to use aliases to make
your queries less verbose.

> Maybe I need a good book on this?

Books Online, see signature.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Multi-Step Transaction, fails but reports success

I have for example the following SQL:
BEGIN TRAN
Delete from users where userid = 102
Update mainSettings set userCnt = UserCnt -1
COMMIT TRAN
If for example the "update" statement fails, but the "delete" statement
is successful - the overall transaction reports no error, and thus, my
appliction thinks everything was a success.
Any ideas?
VB 6.0
MDAC ADO 2.7
JasonAnswered in microsoft.public.sqlserver.programming
Please do not multi-post.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||You have to check for @.@.error after every statement that insert / delete or
update
the db. Also, you have to check the return value of the sp.
create procedure proc1
@.userid
as
set nocount on
declare @.error int
BEGIN TRAN
Delete from users where userid = @.userid
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('Error deleting from table users.', 16, 1)
return 1
end
Update mainSettings set userCnt = UserCnt -1
if @.error != 0
begin
rollback transaction
raiserror('Error updating from table [mainSettings].', 16, 1)
return 1
end
COMMIT TRAN
return @.@.error
go
declare @.rv int
declare @.error int
declare @.tc int
set @.tc = @.@.trancount
exec @.rv = proc1 102
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.error != 0
begin
print 'there was an error.'
end
if @.@.trancount != @.tc
rollback transaction
go
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"jroozee@.gmail.com" wrote:
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
server return a error?
Jason|||Read the articles. There you will find the answer.
AMB
"jroozee@.gmail.com" wrote:
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Same reason, last statement in the batch was successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112043813.998362.180420@.g14g2000cwa.googlegroups.com...
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Additionally, the reason you must check for the error, is that the SQL
programmers can not make the decision for you as to whether or not the error
is sufficient to roll back the transaction. That is for you to decide, so
you much catch the errors and rollback the transaction when necessary.. (
There are types of errors which will automatically roll back the transaction
without your intervention. These are generally more severe errors which
cause batch abort, or you have been chosen as a deadlock victim.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
>I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>

Multi-Row update trigger

Hi,

I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty

I made the following trigger for handling single row updates, which works fine.

UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).Subqueries, perhaps...
UPDATE Purchasing.Product
SET LastReceivedQty = subquery.ReceivedQty,
LastReceivedDate = subquery.ReceivedDate
from Purchasing.DeliveryNote dn
inner join --Subquery
(SELECT dnd.DeliveryNoteID,
sum(i.ReceivedQty) ReceivedQty,
max(dn.ReceivedDate) RecievedDate
FROM Purchasing.DeliveryNoteDetail dnd
INNER JOIN inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID
INNER JOIN Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
group by dnd.DeliveryNoteID) Subquery
on dn.DeliveryNoteID = Subquery.DeliveryNoteIDsql

Multipls cursors help

Hello,
I am trying to create multiple cursors and update 2 separate tables. But the
data tables seem to be locking after I run them. Is my logic off? My code is
below -
set xact_abort on
declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
declare GetUserID cursor for
select [user_id] from users where [user_id] not in (select [user_id] from
userdata)
open GetUserID
fetch next from GetUserID into @.user_id
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetUserData cursor for
select topic_name from userdata where [user_id] = 327
open GetUserData
fetch next from GetUserData into @.topic_name
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetNextID cursor for
select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
open GetNextID
fetch next from GetNextID into @.key_seq_num
while @.@.fetch_status = 0
begin
begin tran
insert into UserData(user_data_id, [user_id], topic_name, update_date,
update_user, create_date, create_user)
select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
getdate(), 'Default'
update nextkey set key_seq_num = @.key_seq_num + 1
from nextkey where key_name = 'userdata_user_data_id'
commit tran
print @.key_seq_num
fetch next from GetNextID into @.key_seq_num
end
close GetNextID
deallocate GetNextID
fetch next from GetUserData into @.topic_name
end
close GetUserData
deallocate GetUserData
fetch next from GetUserID into @.user_id
end
close GetUserID
deallocate GetUserIDAnil,
Can you explain what are you trying to accomplish?
AMB
"Anil" wrote:

> Hello,
> I am trying to create multiple cursors and update 2 separate tables. But t
he
> data tables seem to be locking after I run them. Is my logic off? My code
is
> below -
> set xact_abort on
> declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
> declare GetUserID cursor for
> select [user_id] from users where [user_id] not in (select [user_id] from
> userdata)
> open GetUserID
> fetch next from GetUserID into @.user_id
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetUserData cursor for
> select topic_name from userdata where [user_id] = 327
> open GetUserData
> fetch next from GetUserData into @.topic_name
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetNextID cursor for
> select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
> open GetNextID
> fetch next from GetNextID into @.key_seq_num
> while @.@.fetch_status = 0
> begin
> begin tran
> insert into UserData(user_data_id, [user_id], topic_name, update_date,
> update_user, create_date, create_user)
> select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
> getdate(), 'Default'
> update nextkey set key_seq_num = @.key_seq_num + 1
> from nextkey where key_name = 'userdata_user_data_id'
> commit tran
> print @.key_seq_num
> fetch next from GetNextID into @.key_seq_num
> end
> close GetNextID
> deallocate GetNextID
> fetch next from GetUserData into @.topic_name
> end
> close GetUserData
> deallocate GetUserData
> fetch next from GetUserID into @.user_id
> end
> close GetUserID
> deallocate GetUserID|||I am trying to take all the user id's from cursor 1, some value from cursor
2
and 3 and update them into a table. Cursor 3 has an id that needs to be
incremented each time as it is not an auto number. The way I have laid out m
y
cursors, is that the correct way?
Thanks.
Anil
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Anil,
Can you explain it without referencing the cursors?
AMB
"Anil" wrote:
> I am trying to take all the user id's from cursor 1, some value from curso
r 2
> and 3 and update them into a table. Cursor 3 has an id that needs to be
> incremented each time as it is not an auto number. The way I have laid out
my
> cursors, is that the correct way?
> Thanks.
> Anil
> "Alejandro Mesa" wrote:
>|||Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Alejandro Mesa" wrote:
> Anil,
> Can you explain it without referencing the cursors?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct for multiple cursors? Is it possible to do what I am
trying to do with cursors?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct? Is it possible to do what I am trying to do?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Hi Anil,
Looks like the tables which you are accessing is getting locked since you do
all your stuff within a transaction.
You can do 2 things to see if the issue is happening because of a lock
1. Remove all transaction (begin/commit tran) and see if your code actually
works.
2. If your code works after step 1 then use WITH (NOLOCK) clause in your
select statements.
Regards,
Joe.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Alejandro Mesa" wrote:
>
cursor 2
be
out my
tables. But the
My code is
[user_id] from
'userdata_user_data_id'
update_date,|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>sql

Multiple-step OLE DB operation generated errors.

Database: SQL Server 2000 Standard (works fine)
Database: SQL Server 2005 Standard (fails)
Provider: SQLOLEDB
We can update recordset field (database: MS SQL 2000, client side cursor,
adLockOptimistic), regardless of the recordset's source (query or stored
procedure)
The problem begins with SQL 2005. We cannot update recordset field, if the
recordset is a result of stored procedure.
Error Source: Microsoft Cursor Engine
Error Description: Multiple-step operation generated errors. Check each
status value.> The problem begins with SQL 2005. We cannot update recordset field, if the
> recordset is a result of stored procedure.
Because that's not how you affect data. A recordset is for retrieving and
presenting data. If you want to change the data in the database, use a DML
statement (INSERT/UPDATE/DELETE). Better yet, call a stored procedure that
does that.
A|||Dmitriy Shapiro wrote:
> Database: SQL Server 2000 Standard (works fine)
> Database: SQL Server 2005 Standard (fails)
> Provider: SQLOLEDB
What development platform/language?
> We can update recordset field (database: MS SQL 2000, client side
> cursor, adLockOptimistic), regardless of the recordset's source
> (query or stored procedure)
> The problem begins with SQL 2005. We cannot update recordset field,
> if the recordset is a result of stored procedure.
I'm assuming you've used "SET NOCOUNT ON" in the procedure ...

> Error Source: Microsoft Cursor Engine
> Error Description: Multiple-step operation generated errors. Check
> each status value.
So have you looped through the connection's Errors collection to see the
error message(s)?
If you are developing for ASP, then I will echo Aaron's suggestion: use DML.
If it's a desktop application, then there are some valid reasons (handling
concurrency, etc.) for using a recordset to perform data maintenance.
If none of the above helps, you should post a repro script (DDL and
recordset code) to the group that is focussed on your development platform.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for replies. Our firewall block all notifications. Sorry.
The changes in recordset are not going back to the database. They are only
for UI. This is a legasy code.
Language: VB 6.0
Platform: Windows XP and Win 2003
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:ef4gCUrIGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Dmitriy Shapiro wrote:
> What development platform/language?
> I'm assuming you've used "SET NOCOUNT ON" in the procedure ...
>
> So have you looped through the connection's Errors collection to see the
> error message(s)?
> If you are developing for ASP, then I will echo Aaron's suggestion: use
> DML.
> If it's a desktop application, then there are some valid reasons (handling
> concurrency, etc.) for using a recordset to perform data maintenance.
> If none of the above helps, you should post a repro script (DDL and
> recordset code) to the group that is focussed on your development
> platform.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
> Thanks for replies. Our firewall block all notifications. Sorry.
' Data is trnasmitted but error messages are blocked? I don't think this is
possible.

> The changes in recordset are not going back to the database. They are
> only for UI. This is a legasy code.
I'm not sure I understand what you are saying here, or why it is relevant
that the code is legacy.

> Language: VB 6.0
> Platform: Windows XP and Win 2003
>
So you plan to follow up in a VB group ... ?
Try microsoft.public.vb.database or microsoft.public.vb.database.ado.
Also, you might try using SQL Profiler to trace the actual commands being
sent to the database by the application: it may provide a clue.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> I'm not sure I understand what you are saying here
Here is an example:
'get recordset from database as result of "stored_procedure"
'GetData executes ADO command, with Client Side Cursor and optimistic
locking option
Set rs = GetData("stored_procedure")
'translate column1 to Spanish
'Our legasy code use this technique to translate some data, before
presenting them to the client
rs("column1").value = trnalsateToSpanish(rs("column1").value)
'show recordset in the grid
Display(rs)
These lines work fine when we connect to SQL Server 2000
They fail with SQL Server 2005

>why it is relevant
> that the code is legacy.
I would like to find solution that will have minimum impact on the code.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OXigYR1IGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Dmitriy Shapiro wrote:
> ' Data is trnasmitted but error messages are blocked? I don't think this
> is
> possible.
>
> I'm not sure I understand what you are saying here, or why it is relevant
> that the code is legacy.
>
> So you plan to follow up in a VB group ... ?
> Try microsoft.public.vb.database or microsoft.public.vb.database.ado.
> Also, you might try using SQL Profiler to trace the actual commands being
> sent to the database by the application: it may provide a clue.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
<snip>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Dmitriy Shapiro wrote:
> Here is an example:
> 'get recordset from database as result of "stored_procedure"
> 'GetData executes ADO command, with Client Side Cursor and optimistic
> locking option
> Set rs = GetData("stored_procedure")
>
Unless you have set the connection's CursorLocation property to adUseClient,
this line will result in a default server-side forward-only cursor.
To have control over the cursor type, you must:
Set rs= New ADODB.Recordset
rs.CursorLocation=adUseClient
Then either use the command object as the source argument in the recordset's
Open method:
rs.Open cmd
or use the stored-procedure-as-connection-method technique to bypass the
creation of the explicit Command object:
cn.stored_procedure parm1,...parmN, rs
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob,

>Unless you have set the connection's CursorLocation property to
>adUseClient,
> this line will result in a default server-side forward-only cursor.
I do have MyDBConnection.CursorLocation = adUseClient
Before I was unable to update recordset regardless if was result of stored
procedure or query.
Now I can only do it if it result of the query.
This code works:
Set rs = GetData("select column1 from table1")
rs("column1").value = "abc"
And this code does not work with new SQL Server 2005, but works with SQL
Server 2000:
Set rs = GetData("stored_procedure")
rs("column1").value = "abc"
Thank you.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:epIvE21IGHA.208@.tk2msftngp13.phx.gbl...
> Dmitriy Shapiro wrote:
> Unless you have set the connection's CursorLocation property to
> adUseClient,
> this line will result in a default server-side forward-only cursor.
> To have control over the cursor type, you must:
> Set rs= New ADODB.Recordset
> rs.CursorLocation=adUseClient
> Then either use the command object as the source argument in the
> recordset's
> Open method:
> rs.Open cmd
> or use the stored-procedure-as-connection-method technique to bypass the
> creation of the explicit Command object:
> cn.stored_procedure parm1,...parmN, rs
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
> Bob,
>
> I do have MyDBConnection.CursorLocation = adUseClient
> Before I was unable to update recordset regardless if was result of
> stored procedure or query.
> Now I can only do it if it result of the query.
> This code works:
> Set rs = GetData("select column1 from table1")
> rs("column1").value = "abc"
> And this code does not work with new SQL Server 2005, but works with
> SQL Server 2000:
> Set rs = GetData("stored_procedure")
>
I'm sorry, but without knowing what the GetData function and the stored
procedure look like, nobody will be able to help you. An I'm sure you are
going to be able to get more help from thhe VB experts in one of the VB
newsgroups.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Multiple where clause help please

Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>

Multiple where clause help please

Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
Jake
Nevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
sql

Multiple where clause help please

Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>

multiple updates to table

Hello all,

I have a parallel process updating a single table from various sources using update table statements using a key column.

I'm afraid the process will fail when an update will occur to a record with the same key simultaneously.

Does anyone have a suggestion how to accomplish this? Is there a way timing the updates in queue?

Thanks.

There is no concurrent update in SQL Server, DML statement are queued and executed one by one (if they are not in a transaction). There are sure scenarios which would lead to concurrency conflicts, but this has to be handled by your frontedn application.

HTH, Jens Suessmeyer.'

http://www.sqlserver2005.de

|||

I assume you have multiple clients pumping data into a single table, right?

The next question is what you mean by key? Like an identity key? If so, that won't happen. SQL Server single threads identity key generation so that no two rows will get the same key.

Multiple updates on multiple conditions?

Hi,

I'm looking for a way, if possible, to update a field to different values based on different criteria. For example, Field X should be A if condition 1 is true, it should be B if condition 2 is true, etc. I'm not sure if there's a way to use a CASE in an UPDATE statement?

Thanks

Maybe something like:

update urTable
set X = case when condition_1 = target1 then A
when condition_2 = target2 then B
else C
end
where filterColumn = someCondition

Multiple Updates In Store Procedure?

I am a total newbie to SQL Server, but have a good idea about how things operate..

I have a "job" that runs multiple (10) update queries for me in different tables.

Can I create a stored procedure to do these so that I can make one call to them?

If not, how can I call the job to start external to MS SQL?>>Can I create a stored procedure to do these so that I can make one call to them?

ofcourse. if you have all the required parameters you definetely can.

hth

Multiple Updates at once?

I have an application that, upon exiting, has to update upwards of 300+
records in a SQL Server database.
There are issues with timeouts due to network traffic and server load.
I was wondering if there was a better procedure or method of transferring a
large group of simple sql tasks to the server that could perhaps be done in
one complete action. this may alleviate the timeout issue.
Thank you for your help.I take it you aren't making a single call to a stored proc? Or the 300
records can't be updated with a single UPDATE statement?
Bryce|||300 rows isn't that many to update - provided you don't do it as 300
separate UPDATE statements. Are you using stored procedures to perform the
updates? Where does the data come from?
David Portas
SQL Server MVP
--|||I am not familiar with stored procedures. How would I make a stored
procedure that would change multiple records in a database based on
different criteria, ie replace UPDATE queries like these ones.
UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'A1241234')
UPDATE Movies SET MovieClassNo = 2 WHERE (MovieID = 'F19892346')
UPDATE Movies SET MovieClassNo = 3 WHERE (MovieID = 'L34982423')
UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'Q98645949')
I have looked in the BOL, and I jsut get kinda lost when I do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IY6dnSoM4vCqM63fRVn-oA@.giganews.com...
> 300 rows isn't that many to update - provided you don't do it as 300
> separate UPDATE statements. Are you using stored procedures to perform the
> updates? Where does the data come from?
> --
> David Portas
> SQL Server MVP
> --
>|||Atley wrote:
> I am not familiar with stored procedures. How would I make a stored
> procedure that would change multiple records in a database based on
> different criteria, ie replace UPDATE queries like these ones.
> UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'A1241234')
> UPDATE Movies SET MovieClassNo = 2 WHERE (MovieID = 'F19892346')
> UPDATE Movies SET MovieClassNo = 3 WHERE (MovieID = 'L34982423')
> UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'Q98645949')
>
Are those movieID values hard-coded or are they parameters that need to
be passed into the procedure? What abou the MoveiClassNo? If they are
parameters and you have 300 updates to run, then I'd want to know what
this process is and why you need to update 300 rows in the Movies table
when the application terminates. Passing 300 (or possible 600)
parameters to a procedure is not ideal. In that case, it would probably
be easier to call the procedure 300 times, once for each movieID.
But I'd still like to understand better the reason for the updates and
why there are so many at application termination.
David Gugick
Imceda Software
www.imceda.com|||In principle you can do something like this:
UPDATE Movies
SET movieclassno =
CASE movieid
WHEN 'A1241234' THEN 1
WHEN 'F19892346' THEN 2
WHEN 'L34982423' THEN 3
WHEN 'Q98645949' THEN 1
END
WHERE movieid IN
('A1241234',
'F19892346',
'L34982423',
'Q98645949')
but it's better to use stored procedure parameters to pass those values
rather than construct the SQL dynamically at the client. The question
is, why are you batching up all 300 updates to execute them in one go?
David Portas
SQL Server MVP
--|||These are Movies that are being transfered from one location to another in a
bulk transfer
So the movies are being scanned in, and then the location ID has to be
changed in one fell sweep.
The parameters are not known until the waybill is made and the movies are
scanned in.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110536761.241514.182540@.g14g2000cwa.googlegroups.com...
> In principle you can do something like this:
> UPDATE Movies
> SET movieclassno =
> CASE movieid
> WHEN 'A1241234' THEN 1
> WHEN 'F19892346' THEN 2
> WHEN 'L34982423' THEN 3
> WHEN 'Q98645949' THEN 1
> END
> WHERE movieid IN
> ('A1241234',
> 'F19892346',
> 'L34982423',
> 'Q98645949')
> but it's better to use stored procedure parameters to pass those values
> rather than construct the SQL dynamically at the client. The question
> is, why are you batching up all 300 updates to execute them in one go?
> --
> David Portas
> SQL Server MVP
> --
>|||These are Movies that are being transfered from one location to another in a
bulk transfer
So the movies are being scanned in, and then the location ID has to be
changed in one fell sweep.
The parameters are not known until the waybill is made and the movies are
scanned in.
This has to be done before the application is closed or the waybill is
printed, or the waybill is closed.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uAWkxjdJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> Atley wrote:
> Are those movieID values hard-coded or are they parameters that need to
> be passed into the procedure? What abou the MoveiClassNo? If they are
> parameters and you have 300 updates to run, then I'd want to know what
> this process is and why you need to update 300 rows in the Movies table
> when the application terminates. Passing 300 (or possible 600)
> parameters to a procedure is not ideal. In that case, it would probably
> be easier to call the procedure 300 times, once for each movieID.
> But I'd still like to understand better the reason for the updates and
> why there are so many at application termination.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Atley wrote:
> These are Movies that are being transfered from one location to
> another in a bulk transfer
> So the movies are being scanned in, and then the location ID has to be
> changed in one fell sweep.
> The parameters are not known until the waybill is made and the movies
> are scanned in.
> This has to be done before the application is closed or the waybill is
> printed, or the waybill is closed.
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uAWkxjdJFHA.3960@.TK2MSFTNGP09.phx.gbl...
I would try writing a stored procedure to do the update and calling it
300 times from the application. It should happen very quickly. Pass the
MovieClassNo and MoveID to the procedure. This option keeps all the
intelligence in the database.
Before I would consider anything else, I would try that.
David Gugick
Imceda Software
www.imceda.com|||CREATE TABLE WayBills
(movieid CHAR (8) NOT NULL PRIMARY KEY,
movie_class_nbr INTEGER NOT NULL
CHECK (movie_class_nbr BETWEEN 1 AND 4));
Then use the collected data:
UPDATE Movies
SET movie_class_nbr
= (SELECT movie_class_nbr
FROM WayBills AS W1
WHERE W1.movie_id = Movies.movie-id)
WHERE EXISTS (SELECT *
FROM WayBills AS W1
WHERE W1.movie_id = Movies.movie_id);

Multiple Update with multiple condition

I'm having an Employee table with a Salary field. How can we increate the
salary of the employees with following conditions:
1) salary between 1000 and 10000 : increase 25%
2) salary between 10000 and 20000 : increase 15%
3) salary between 20000 and 30000 : increase 5%

Surely you can create a cursor to solve this. But the question is, Is it
possible to solve this in a single query, if no what is most optimized
way?Try:

UPDATE Employee
SET salary = salary *
CASE
WHEN salary>=1000 AND salary<10000 THEN 1.25
WHEN salary>=10000 AND salary<20000 THEN 1.15
WHEN salary>=20000 AND salary<30000 THEN 1.5
END
WHERE salary>=1000 AND salary<30000

--
David Portas
SQL Server MVP
--|||Thanks friend|||Thanks friend|||Thank you friend

Multiple Triggers Error: Nesting Level Exceeded (Limit 32). SQL 2005

I am running sql 2005 here is my issue I have 4 triggers on a table 2 inserts, and 2 updates, I can run the two inserts and only 1 update at one time or I get the Nesting Level 32 error. Each item was tested seperately and works fine also the group of 3 does exactly as it is suppose to, but the 2 update always triggers conflict with each other please if anyone has a suggestion im all ears.

Update Race:

IFNotUPDATE(Edited)

UPDATE Patient

SET Race =(SELECTCASE Racecode

WHEN'API'THEN'Asian, Pacific Islander'

WHEN'BH'THEN'Black, Hispanic'

WHEN'BNH'THEN'Black, Non-Hispanic'

WHEN'HIS'THEN'Hispanic'

WHEN'OTH'THEN'Other, Black & White'

WHEN'WH'THEN'White, Hispanic'

WHEN'WNH'THEN'White, Non-Hispanic'

END)

WHERE id# IN(SELECT id# FROM inserted)

END

Update Religion:

IF Not UPDATE (Edited)

Update Patient

SET Religion = (SELECT CASE Religioncode

WHEN 'C' THEN 'Catholic'

WHEN 'J' THEN 'Jewish'

WHEN 'O' THEN 'Other'

WHEN 'P' THEN 'Protestant'

WHEN 'U' THEN 'Unknown'

END)

WHERE id# IN (SELECT id# FROM Inserted)

END

why not combining them into one update.

IF Not UPDATE (Edited)
begin
UPDATE Patient
SET Race = (SELECT CASE Racecode
WHEN 'API' THEN 'Asian, Pacific Islander'
WHEN 'BH' THEN 'Black, Hispanic'
WHEN 'BNH' THEN 'Black, Non-Hispanic'
WHEN 'HIS' THEN 'Hispanic'
WHEN 'OTH' THEN 'Other, Black & White'
WHEN 'WH' THEN 'White, Hispanic'
WHEN 'WNH' THEN 'White, Non-Hispanic'
END)
,Religion = (SELECT CASE Religioncode
WHEN 'C' THEN 'Catholic'
WHEN 'J' THEN 'Jewish'
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Protestant'
WHEN 'U' THEN 'Unknown'
END)
WHERE id# IN (SELECT id# FROM inserted)
end|||I am not really good at setting up my code thanks very much I have some classes coming up. That did the trick. Thanks again!!!

Multiple triggers

When a trigger on one table updates another table, does it activate
the update trigger on the updated table?

For ex:

1. Table A has a trigger for update
2. Table B has a trigger for update that updates table A

Will the trigger no table A run when table B trigger updates table A?

ThanksHi

If you update table B the trigger will update table A and the trigger on
table A will be activated. You will have to be careful that the actions you
perform within the do what you expect. There are 32 levels of recursion.

Check out Using Nested Triggers in Books Online.

John
"Yariv" <yariva@.mll.co.il> wrote in message
news:b4e534ce.0310080112.14d7aa18@.posting.google.c om...
> When a trigger on one table updates another table, does it activate
> the update trigger on the updated table?
> For ex:
> 1. Table A has a trigger for update
> 2. Table B has a trigger for update that updates table A
> Will the trigger no table A run when table B trigger updates table A?
> Thanks|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you update table B the trigger will update table A and the trigger on
> table A will be activated. You will have to be careful that the actions
> you perform within the do what you expect. There are 32 levels of
> recursion.

I'll add here, that is depends on the configuration option "nested
triggers". By defualt it is on. However, it is a little too easy to
turn it off in the Enterprise Manager. (Been there, done that.)

Note that if the trigger updates the table the trigger belongs to,
the default is that the trigger is *not* re-fired. This depends on
a database setting, RECURSIVE_TRIGGERS.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

Multiple tables used in select statement makes my Update statement not work?

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown.

I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:

----------------------------------------

SELECT:

SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName

FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID)

WHERE (People.PersonID = ?)

UPDATE:

UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ?

----------------------------------------

The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed.

If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.

Thank you in advance for any time, help, and/or advice you can give.

Brian

I just wanted to add that I am working with Visual Web Developer 2005 Express. If any one knows how I can get around this issue please let me know, or even if you might know of a place that could possibly have a document that approaches this I have no problem doing the research. I have spent hours searching forums and doing google and microsoft searches for tutorials and have had no luck. I definately havn't come across anything say this is not possible, I just need to know what I am missing.

Thanks for your time in advance,

Brian

|||

You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.

That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".

|||

Motley:

You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.

The question marks were put in place by the software. I was trying to get the UPDATE to work on my own and i was setting the fields I wanted to be updated equal to @.fieldName. I could not get this to update so I used the "Advanced" feature in the quesry wizard (I guess it's called) and selected the button to auto-generate the SQL statement and this worked. The UPDATE statement it generated is the one I pasted above. My database is an access database for the time being, but I am using the SqlDataSource method to connect to my database, must be thats why it used the question marks?

Motley:

That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".

The way I have it setup is so that the field contains a INT value, but the value it shows to the user is a text value. The drop down list you are suggesting is exactly what I am using. When the user is in Read Only mode, everything is shown as a label. When they enter Edit mode, all the fields with foreign keys turn into drop down lists. The drop down list shows a string value, but when the choice is made, its value is and integer. I believe this is working right because the technique I use in order to select a user to edit is to select a name from a drop down list that is external from my gridview (same thing with my detailview). The value of the drop down list is numerical based on the primary key of the PersonID, but the value shown in the drop down list is the name that corrisponds to the given PersonID. This works great and only shows me the record of the person I want to see. Because this worked, I just assumed that useing the same technique in the Edit view would be working the same way. I think this is what you mentioned in what you mentioned above. I will paste all the acctual code from the page I am working on that uses DetailsView.

<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
DeleteCommand="DELETE FROM [People] WHERE [PersonID] = ?" InsertCommand="INSERT INTO [People] ([FirstName], [LastName], [FullName], [PropertyID], [InviteTypeID], [RSVP], [Wheelchair], [MealID], [TransportationID]) VALUES (?, ?, ?, ?, ?, ?, ?,?,?)"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT People.*, Property.[House/Day Hab], InviteType.InviteTypeName, Meals.MealName, Transportation.TransportationName FROM Transportation INNER JOIN (Meals INNER JOIN (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) ON Meals.MealID=People.MealID) ON Transportation.TransportationID=People.TransportationID WHERE (PersonID = ?)"
UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?">
<DeleteParameters>
<asp:Parameter Name="PersonID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="FullNameDDL" Name="People.PersonID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>

<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="PropertyID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="MealID" Type="Int32" />
<asp:Parameter Name="TransportationID" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
<asp:DropDownList ID="FullNameDDL" runat="server" DataSourceID="GetNames" DataTextField="FullName"
DataValueField="PersonID" Style='left: 5px; position: relative; top: 190px' AutoPostBack='True'>
</asp:DropDownList><asp:AccessDataSource ID="GetNames" runat="server" DataFile="H:\Visual Studio 2005\WebSites\test\App_Data\DinnerGuest.mdb"
SelectCommand="SELECT [PersonID], [FullName] FROM [People]"></asp:AccessDataSource>

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
Height="50px" Style='left: 201px; position: relative; top: 152px' Width='125px'>
<RowStyle Wrap="False" />
<Fields>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" ConvertEmptyStringToNull="False" NullDisplayText="<NULL>" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" ConvertEmptyStringToNull="False" />
<asp:TemplateField HeaderText="PropertyID" SortExpression="PropertyID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" SelectedValue='<%# Bind("PropertyID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("[House/Day Hab]") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="InviteTypeID" SortExpression="InviteTypeID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" SelectedValue='<%# Bind("InviteTypeID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList4" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("InviteTypeName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CheckBoxField DataField="RSVP" HeaderText="RSVP" SortExpression="RSVP" />
<asp:CheckBoxField DataField="Wheelchair" HeaderText="Wheelchair" SortExpression="Wheelchair" />
<asp:TemplateField HeaderText="MealID" SortExpression="MealID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList5" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" SelectedValue='<%# Bind("MealID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList6" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("MealName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TransportationID" SortExpression="TransportationID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList7" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" SelectedValue='<%# Bind("TransportationID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList8" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("TransportationName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
</div>
</form>

|||

Yes, using access would require using ?'s since it doesn't support named parameters.

The DropDownField control I mentioned makes things a bit easier for you because you don't have to create templated fields (They become DropDownFields), your main select is kept simple if you want (No joins needed, it can pull the text values for you if you want).

But since you've already gone through the trouble, it appears the only problem I can find is that your update parameters do not match your update command.

UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?"

UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>

LocationID should be PropertyID. MealID needs to be added. TransportationID needs to be added. PersonID should either be a controlparameter -- pulling it's value from the person dropdown, or you should add it to the detailview's datakeynames property. All the parameters need to be in the exact order they are mentioned in the update command.

|||

Thank you so much for all your help. I was definately only relying on Visual Web Developer to get this going which seems to be my down fall. As I made changes to my data source and changed my queries, I guess the code that is automatically developed doesn't follow along with the changes I make very well. Thanks again for taking the time to look through my code, I will definately make sure I am checking it more, instead of just relying on the software. I guess if the software could do everything I wanted without me seeing the code then I probably would have a much more difficult time finding work haha. Live and learn I guess.

Thanks again,

Brian

Multiple table update

Hi There

I am using tsql in Sql Server 2005.

Is it possible/how do i update multiple tables in a single update statement.

I want to do something like this.

UPDATE A, B, C, D SET A.Col1 = ?, B.Col2 = ?, C.Col3 = ?, D.Col4 = ?

FROM TableA AS A

JOIN TABLEB AS B ON B.ID = A.ID

JOIN TABLEC AS C ON C.ID = A.ID

JOIN TABLED AS D ON D.ID = A.ID

Basically all the tables are related, i want to update values in all the tables in a single update but i just cannot seem to get the syntax correct, BOL also has no example of multiple table update so i am not even sure if this is possible.

Thanx

you can not update multiple tables in single update statement . what u can do is put all these different update statement in a begin tran/commit tran

Madhu