Update Multiple Problem - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Multiple Problem

Author

Topic

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-23 : 10:46:36

So this is a problem I encountered while trying to modify my database structure without losing any data. I'm taking some information in MyObjects and moving it out into MyPropertys. The problem is that MyObject must reference the MyPropertys by ID, which I don't know until they are created. So what I do is order the MyObjects by ID, and create the first MyProperty for each of them with type 0. Then I select all MyPropertys of type 0 and sort them by ID so they have a 1 to 1 correspondence with the MyObjects and insert the IDs. I repeat this for types 1 and 2. Here's the SQL:

INSERT INTO [dbo].[MyObjectPropertys] SELECT 0, CASE WHEN [PropertyASwitching] = 3 THEN 1 ELSE 2 END FROM [dbo].[MyObjects] ORDER BY [MyObjects].[Id] ASC
GO
UPDATE [dbo].[MyObjects] SET [PropertyAId] = (SELECT TOP 100 PERCENT [Id] FROM [dbo].[MyObjectPropertys] WHERE [Parameter] = 0 ORDER BY [MyObjectPropertys].[Id] ASC)
GO
INSERT INTO [dbo].[MyObjectPropertys] SELECT 1, 2 FROM [dbo].[MyObjects] ORDER BY [MyObjects].[Id] ASC
GO
UPDATE [dbo].[MyObjects] SET [PropertyBId] = (SELECT TOP 100 PERCENT [Id] FROM [dbo].[MyObjectPropertys] WHERE [Parameter] = 1 ORDER BY [MyObjectPropertys].[Id] ASC)
GO
INSERT INTO [dbo].[MyObjectPropertys] SELECT 2, CASE WHEN [PropertyCSwitching] = 3 THEN 1 ELSE 2 END FROM [dbo].[MyObjects] ORDER BY [MyObjects].[Id] ASC
GO
UPDATE [dbo].[MyObjects] SET [PropertyCId] = (SELECT TOP 100 PERCENT [Id] FROM [dbo].[MyObjectPropertys] WHERE [Parameter] = 2 ORDER BY [MyObjectPropertys].[Id] ASC)
GO

The problem is I'm getting the error "Subquery returned more than 1 value" because the UPDATE wants one value and I'm giving it a lot of values. What I really need is to update each MyObject one at a time. Anybody know how?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 12:20:59

can you explain whats the purpose of below subquery?

(SELECT TOP 100 PERCENT [Id] FROM [dbo].[MyObjectPropertys] WHERE [Parameter] = 1 ORDER BY [MyObjectPropertys].[Id] ASC)

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

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-23 : 13:33:38

I have an object (MyObject) which has 3 sets of properties, in the old database design they were all in the MyObject object, now I want to move them out into another table (MyObjectPropertys). MyObject will then hold 3 IDs of those properties. Each one must be a certain 'type', so the first foreign key link (PropertyAId) must be of type 0, PropertyBId must be type 1, PropertyCId must be type 2.

So the first INSERT statement creates a MyObjectProperty of type 0 for each of the MyObjects in the database. And since I sorted the MyObjects while creating the MyObjectPropertys, the IDs of the MyObjectPropertys will be in the same order as the IDs of the MyObjects. What I am trying to do in that line is get all of the MyPropertys of a certain type and put the ID of the first MyObjectProperty in the first MyObject, and do this for all of them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:48:09

whats the purpose of top 100 percent then? you want only 1 to get updated right?

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

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-23 : 16:19:05

I want all of them updated, if I had 50 MyObjects originally I will now have 50 MyObjectPropertys. I need the properties matched to the objects by their order:

MyObject ID MyObjectProperty ID
5 1
7 2
78 3
123 4
324 5
... ...

So I have two lists ordered by their ID, though they may not be the same IDs. I need to do this:
MyObject[5].PropertyAId = 1
MyObject[7].PropertyAId = 2
etc...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:35:17

seems like what you're after is scenario 2 which is explained below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-24 : 09:51:18

Perhaps this query shows better what I would like to do, but I can't get it to work for me:

UPDATE [dbo].[MyObjects] SET [PropertyAId] = (INSERT INTO [dbo].[MyObjectPropertys] VALUES 0, CASE WHEN [AnotherGeneratorProperty] = 3 THEN 1 ELSE 2 END SELECT SCOPE_IDENTITY())

This would also be fine, as I can update the value after:

UPDATE [dbo].[MyObjects] SET [PropertyAId] = (INSERT INTO [dbo].[MyObjectPropertys] VALUES 0, 1 SELECT SCOPE_IDENTITY())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 10:04:37

you cant nest an INSERT inside UPDATE like this.
post some sample data and explain what you want

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

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-24 : 10:48:02

Start:

[MyObjects]
Id    SomeProperty    PropertyAId
3     1               NULL
5     2               NULL
7     2               NULL
12    1               NULL
34    2               NULL
43    2               NULL
77    2               NULL
124   2               NULL

End:

[MyObjects]
Id    SomeProperty    PropertyAId
3     1               1
5     2               2
7     2               3
12    1               4
34    2               5
43    2               6
77    2               7
124   2               8

[MyObjectPropertys]
Id    SomeProperty
1     1
2     2
3     2
4     1
5     2
6     2
7     2
8     2

Now I don't even really need to move the value of SomeProperty because I can always do that after, but that's what I'm looking to do. This is also acceptable:

Start:

[MyObjects]
Id    PropertyAId
3     NULL
5     NULL
7     NULL
12    NULL
34    NULL
43    NULL
77    NULL
124   NULL

End:

[MyObjects]
Id    PropertyAId
3     1
5     2
7     3
12    4
34    5
43    6
77    7
124   8

[MyObjectPropertys]
Id
1
2
3
4
5
6
7
8

All I need to do is create a MyObjectProperty row for each of the MyObject rows and set MyObject.PropertyAId to MyObjectProperty.Id

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-24 : 13:04:08

It's easy to create a MyObjectProperty for each MyObject, especially since they can all be the same, and I'll just modify them after. The hard part is putting a MyObjectProperty ID in each of the MyObjects.

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-24 : 13:58:28

If anyone has any clues how to do this, please help. I basically need to fix this tonight.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-24 : 14:08:04

Not exactly sure if this is what you want...

--Sample Data
create table #MyObjects
(Id int, PropertyAId int)

create table #MyObjectPropertys (Id int)

insert #MyObjects
select 3,     NULL
union all select 5,     NULL
union all select 7,     NULL
union all select 12,    NULL
union all select 34,    NULL
union all select 43,    NULL
union all select 77,    NULL
union all select 124,   NULL

--Query to Update and Insert
update a
set a.PropertyAId = a.seq
from (select row_number() over(order by Id) as seq, * from #MyObjects) a

insert into #MyObjectPropertys (Id)
select PropertyAId from #MyObjects

--Result
#MyObjects
Id          PropertyAId
----------- -----------
3           1
5           2
7           3
12          4
34          5
43          6
77          7
124         8

#MyObjectPropertys
Id
-----------
1
2
3
4
5
6
7
8

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-24 : 14:43:15

That almost works, except that it doesn't actually use the generated IDs of the MyObjectPropertys, it just counts from 1. I need to do this three times, so what I actually need is this:

Before:

[MyObjects]
Id    PropertyAId    PropertyBId    PropertyCId
3     NULL           NULL           NULL
5     NULL           NULL           NULL
7     NULL           NULL           NULL
12    NULL           NULL           NULL
34    NULL           NULL           NULL
43    NULL           NULL           NULL
77    NULL           NULL           NULL
124   NULL           NULL           NULL

After:

[MyObjects]
Id    PropertyAId    PropertyBId    PropertyCId
3     1              9              17
5     2              10             18
7     3              11             19
12    4              12             20
34    5              13             21
43    6              14             22
77    7              15             23
124   8              16             24

[MyObjectPropertys]
Id    Type
1     0
2     0
3     0
4     0
5     0
6     0
7     0
8     0
9     1
10    1
11    1
12    1
13    1
14    1
15    1
16    1
17    2
18    2
19    2
20    2
21    2
22    2
23    2
24    2

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-24 : 15:46:29

Again it's easy to create the MyObjectPropertys, I just need to get the IDs into the MyObjects.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 10:05:50

[code]
UPDATE t
SET t.PropertyAId=q.AId,
t.PropertyBId=q.BId,
t.PropertyCId=q.CId
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Seq,
PropertyAId, PropertyBId, PropertyCId
FROM MyObjects
)t
JOIN (SELECT Seq,
MAX(CASE WHEN Type=0 THEN Id ELSE NULL END) AS AId,
MAX(CASE WHEN Type=1 THEN Id ELSE NULL END) AS BId,
MAX(CASE WHEN Type=2 THEN Id ELSE NULL END) AS CId
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY type ORDER BY Id) AS Seq,Id,Type FROM MyObjectPropertys) r
GROUP BY Seq
)q
ON q.Seq=t.Seq
[/code]

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

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-08-25 : 11:51:44

You are my saviour. It works perfectly, I never would have gotten it in a million years. Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 13:46:12

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources