Update a table from a Select of another table 2

[CasperTFG]

Any help would be appretiated here...

I have 3 tables and have been assigned the task of denormalizing the data to help with history queries. I want to write an SQL that will take data from the Third Table and put it into the first table based on the Table2 Record which links the first and third. Here is the Sample Table Set ups...
[tt]
[Table1]
[BUID]
[Name]

[T_Table2]
[BUID]
[UID]

[T_Table3]
[UID]
[Name]
[/tt]
So what I want to do is take the ObjectName Data that is in Table3, and Populate it into Table1, based on the BUID from Table1 and 2 and the UID between 2 and 3.

Here is what I have:
[tt]
UPDATE Table1 SET [Name]=(
SELECT DISTINCT(T3.Name) AS [Name]
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T2.BUID = T1.BUID
INNER JOIN Table3 AS T3 ON T3.UID = T2.UID)
[/tt]
But it is Returning the error:
Server: Msg 512, Level 16, State 1, Line 39
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I did Counts and Distinct Counts on all of these tables...
[tt]SELECT COUNT(DISTINCT(BUID)), COUNT(BUID) FROM Table1
Results: 7078, 7078

SELECT COUNT(DISTINCT(BUID)), COUNT(BUID) FROM Table2
Results: 3561, 3561
[/tt]


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[gmmastros]

If this data is important to you, please test this on a development version of the database before running on the real data.

UPDATE T1 
SET    T1.[Name]= T3.Name
FROM   Table1 AS T1
       INNER JOIN Table2 AS T2 ON T2.BUID = T1.BUID
       INNER JOIN Table3 AS T3 ON T3.UID = T2.UID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

UPDATE t1 SET t1.[Name]=T3.[Name]
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T2.BUID = T1.BUID
INNER JOIN Table3 AS T3 ON T3.UID = T2.UID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Hahaha George we did it again, same time same answer



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CasperTFG]

If this works I'll give you both stars and yes, I am working from a back-up of production


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

Beautiful... I don't know why I spent 3 hours banging my head against the wall before coming here... You guys are great. Stars all around.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[gmmastros]

That 3 hours of head banging was probably worth it. I doubt you'll ever forget how to do this. I'm glad you got this working.

Denis, you need to type faster!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CasperTFG]

Okay, that solved the easy ones. But there is a tougher one, The reason I was using the Select in the first place.

I now have to get data that may be in one of 6 tables, the tables are much more complex, but to Simplify I have this...
[tt]
[MainTable]
[BUID]
[Name]

[LinkTable]
[BUID]
[UID_1]
[UID_2]
[UID_3]

[Table1]
[UID]
[NamePart1]

[Table2]
[UID]
[NamePart1]
[NamePart2]

[Table3]
[UID]
[NamePart1]
[NamePart2]
[NamePart3]
[/tt]
Now I know which table to look in Based on LinkTable, only 1 of the 3 UID's will be filled, this tells me which table to get my data from. Then if it is Table 3, I have to set Name= NamePart1+'/'+NamePart2+'/'+NamePart3

This is what I had... But again... Not working...
[tt]
UPDATE MainTable SET [Name]=(
SELECT T1.NamePart1 AS [Name]
FROM MainTable AS MT
INNER JOIN LinkTable AS LT ON LT.UID = MT.BUID
INNER JOIN Table1 AS T1 ON T1.UID = LT.UID_1
UNION SELECT T2.NamePart1+'/'+T2.NamePart2 AS [Name]
FROM MainTable AS MT
INNER JOIN LinkTable AS LT ON LT.UID = MT.BUID
INNER JOIN Table2 AS T2 ON T2.UID = LT.UID_2
UNION SELECT T3.NamePart1+'/'+T3.NamePart2+'/'+T3.NamePart3 AS [Name]
FROM MainTable AS MT
INNER JOIN LinkTable AS LT ON LT.UID = MT.BUID
INNER JOIN Table3 AS T3 ON T3.UID = LT.UID_3)
[/tt]

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

any thoughts

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[gmmastros]

>> yes, I am working from a back-up of production

[blue]good[/blue]

If I understand your requirements, then this query may work. I'm a little concerned about dups between the tables, so you should double check your data. Hopefully it won't be a problem.

Notice that I put Where Clauses on the union selects. Since I'm not confident that I understand your requirements, take a lok at it to make sure it's the right thing to do.

Update MainTable
Set    MainTable.Name = Temp.Name
From   MainTable
       Inner Join (
         SELECT MT.BUID, T1.NamePart1 AS [Name]
	 FROM   MainTable AS MT
                INNER JOIN LinkTable AS LT ON LT.UID = MT.BUID
                INNER JOIN Table1 AS T1 ON T1.UID = LT.UID_1
         Where  T1.NamePart Is Not NULL
					
         UNION 
					
         SELECT MT.BUID, 
                T2.NamePart1+'/'+T2.NamePart2 AS [Name]
         FROM   MainTable AS MT
                INNER JOIN LinkTable AS LT ON LT.UID = MT.BUID
                INNER JOIN Table2 AS T2 ON T2.UID = LT.UID_2
         Where  T2.NamePart1 Is Not NULL
                And T2.NamePart2 Is Not NULL

         UNION 
					
         SELECT MT.BUID,
                T3.NamePart1
                +'/'+T3.NamePart2
                +'/'+T3.NamePart3 AS [Name]
         FROM   MainTable AS MT
                INNER JOIN LinkTable AS LT ON LT.UID = MT.BUID
                INNER JOIN Table3 AS T3 ON T3.UID = LT.UID_3)
         Where  T3.NamePart1 Is not NULL
                And T3.NamePart2 Is Not NULL
                And T3.NamePart3 Is Not NULL
      ) A On MainTable.BUID = A.BUID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CasperTFG]

Actually I did it by doing 3 seperate update scripts. The Link Table will only ever have 1 ID Number filled in, so the Inner Join would not hit the wrong ones...

Thanks for all the assistance.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".