SQL Query v4

[GrimStoner]

This is becoming a habit...

I have 3 tables in SQL Server 2005...

(excuse the ascii)

Table1 Table2 Table3
-------- -------- --------
ID ID2 ID3
SomeField FKID FKID2
otherstuff SomeField2

Table3 is in a one-to-many relationship with Table2, and Table2 with Table1.

How can I set the value of SomeField2 to the same as SomeField1?

Just to describe my problem...

Table1Row1 ------- Table2Row1 ------------- Table3Row1
| |
| --------- Table3Row2
---- Table2Row2 -------------- Table3Row3
|
---------- Table3Row4

Table3Row1 through Table3Row4's SomeField2 column needs to be set to Table1Row1 SomeField column value.

I have tried :

Code: Select all

drop table #test;
--Create #table in mem
select Table1.ID, Table1.SomeField, a.componentid, a.competencyid, b.id as UniqueID, b.groupid as ToChangeID 
into #test
from Table1, Table2 a, Table3 b 
where a.FKID = Table1.ID
and b.ID = a.FKID;
--Set values
update Table3 set SomeField2 = (select SomeField from #test where #test.ID = Table3.ID);

but the subselect returns more than one row, and then SQL moans about it.

[RuadRauFlessa]

Dude your ASCI Sucks. Better post the create scripts for the tables in some code blocks. I have a test DB I can run them in and then test to see what the best solution would be. Also if we can clearly see what the DB Schema is then it will be much easier to understand what you mean.

[cbrunsdonza]

Code: Select all

update Table3 set SomeField2 = (select SomeField from #test where #test.ID = Table3.ID );

Update on JOINS is not SQL standard and this appears what you are trying to do:

Code: Select all

UPDATE Table3 
  SET SomeField2=(SELECT SomeField FROM #test WHERE #test.ID = Table3.ID LIMIT 1)
WHERE ID IN (SELECT ID FROM #test);

You need to add a LIMIT to your SELECT SomeField just in case you have duplicate ID. Then you also need to make sure you are only updateing the data that has matching IDs across your two tables - that is the WHERE ID IN () comes from.

I work with PostgreSQL everyday and the above update is a common mistake.