Select from Table and update same table in 1 cmd

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Select from Table and update same table in 1 cmd

Author

Topic

sharkspm
Starting Member

3 Posts

Posted - 2010-05-15 : 08:31:37

Hi all.

I have one Table setup as follows
ProductID
CatID
DisplayOrderID

Example Data
ProductID,CatID,DisplayOrderID
1,1,0
2,1,1
3,1,2
4,2,0
5,2,1
6,1,3

What I am trying to do is select any record where the DisplayOrder = 0 and then update that record so the DisplayOrder is set to the Maximum DisplayOrder + 1 for that CatID. So for example the result set would look like

ProductID,CatID,DisplayOrderID
1,1,4
2,1,1
3,1,2
4,2,2
5,2,1
6,1,3

I am doing this for a web page and so far how I have approached it is by 2 pieces of code

the first bit opens the table and then runs

select * from LT_Product_Table where DisplayOrder = 0"

and with each record set it pulls back I take the CatID value and put into a variable called VCatID runs the following

update LT_Product_Table set DisplayOrder = ( select max(DisplayOrder) +1 from LT_Product_Table where CatID= " & VCatID & " ) where CatID = " & VCatID & " and DisplayOrder = 0 "

When I have tried running this I have mixed results, from my my log output I can see the sql produced for each category it finds a DisplayOrder of 0 and I can copy and paste that into a sql session and it will do what I want but the problem is it takes forever to run through a web page as I am calling 2 seperate pieces of sql, I could put the kids to bed and start dinner when its running :).

So I am guessing there must be a way I can select and update the records in one statement for any record with a DisplayOrder of 0. I guess this is probably some of the worst sql you have seen but I am trying to learn.

I feel an idiot for having to ask but thanks for your time anyway

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-15 : 11:34:30

try this ...

update YOURTABLE set DisplayOrderID = (select MAX(DisplayOrderID)+1 from YOURTABLE) output inserted.* where DisplayOrderID = 0

----------- run this to see result --------
declare @temp table(ProductID int , CatID int, DisplayOrderID int)

insert into @temp values(1,1,0)
insert into @temp values(2,1,1)
insert into @temp values(3,1,2)
insert into @temp values(4,2,0)
insert into @temp values(5,2,1)
insert into @temp values(6,1,3)
select * from @temp

update @temp set DisplayOrderID = (select MAX(DisplayOrderID)+1 from @temp)
output inserted.*
where DisplayOrderID = 0

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-16 : 07:43:08

quote:
Originally posted by namman

try this ...

update YOURTABLE set DisplayOrderID = (select MAX(DisplayOrderID)+1 from YOURTABLE) output inserted.* where DisplayOrderID = 0

----------- run this to see result --------
declare @temp table(ProductID int , CatID int, DisplayOrderID int)

insert into @temp values(1,1,0)
insert into @temp values(2,1,1)
insert into @temp values(3,1,2)
insert into @temp values(4,2,0)
insert into @temp values(5,2,1)
insert into @temp values(6,1,3)
select * from @temp

update @temp set DisplayOrderID = (select MAX(DisplayOrderID)+1 from @temp)
output inserted.*
where DisplayOrderID = 0

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

it should be



update t set t.DisplayOrderID = (select MAX(DisplayOrderID)+1 from @temp where CatID = t.CatID)
output inserted.*
FROM @temp t
where t.DisplayOrderID = 0

as per OP's reqmnt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sharkspm
Starting Member

3 Posts

Posted - 2010-05-16 : 15:50:46

Thanks for the feedback.

I created a testing table and ran the update below as you suggested

update t set testing.DisplayOrderID = (select MAX(DisplayOrderID)+1 from testing where CatID = testing.CatID)
output inserted.*
FROM testing t
where testing.DisplayOrderID = 0

but I get the following error

Missing INTO keyword , Column or expression DisplayOrderID cannot be updated.

I am inexperienced with sql but I am trying to learn :). Can you see where I am going wrong.

Thanks in advance

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-17 : 12:22:02

Try this ....

update testing set DisplayOrderID = (select MAX(DisplayOrderID)+1 from testing) output inserted.* where DisplayOrderID = 0

if error, post error message or try visakh16 solution

sharkspm
Starting Member

3 Posts

Posted - 2010-05-18 : 07:11:23

When running the following
update testing set DisplayOrderID = (select MAX(DisplayOrderID)+1 from testing) output inserted.* where DisplayOrderID = 0

This is the error I get

Missing INTO keyword.
Column or expression 'DisplayOrderID' cannot be updated.

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

I then tried the following SQL

update t set t.DisplayOrderID = (select MAX(DisplayOrderID)+1 from testing where CatID = t.CatID)
output inserted.*
FROM testing t
where t.DisplayOrderID = 0

And it still comes back with

Missing INTO keyword.
Column or expression 'DisplayOrderID' cannot be updated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources