This SQL query is not working for me please help

Question

I have a poorly normalized set of tables and am trying to correct this issue. The DB is MS SQL 2005.

Table1 has an id field (record ID), a mainID field (person ID) and a sequence field (and int that determines the sort order)

Table2 has its own id field and a copy of the id for the first record by a person (sequence = 1).

I've added a new field to table2 call table2_id and would like to populate this field with the ID from table2 so that I can do away with table1.mainID. table2 only has a record for one of the records for each person and mainId is set to the id where table1.sequence = 1.

This is the update query I though would do the job by I'm getting errors

update table1 as a  
set regInfoID = (select b.id 
                 from table2 as b 
                 where b.ref1 = (select c.id 
                                 from table1 as c 
                                 where c.mainID = a.mainID 
                                       and sequence = 1))  

I believe I'm on the right track her since the following query works fine

select regInfoID = (select b.id 
                    from table2 as b 
                    where b.ref1 = (select c.id 
                                    from table1 as c 
                                    where c.mainID = a.mainID 
                                          and sequence = 1)), a.*  
from table1 as a  

Answer 1

I think your query is equivalent to this:

update a
set regInfoID = b.id
-- select a.*, b.id
from table2 b 
inner join table1 c on c.id = b.ref1
inner join table1 a on c.mainID = a.mainID and c.sequence = 1

From this query, I think you will potentially have indeterminate results because table2 (b) is not guaranteed to be a single row result. So regInfoID will be set to one of the resulting b.id values.

Answer 2

I figured it out

update table1
set regInfoID = (select b.id 
                 from table2 as b 
                 where b.ref1 = (select c.id 
                                 from table1 as c 
                                 where c.mainID = a.mainID 
                                       and sequence = 1)) 
from table1 as a 

The error seems to have been cause by my having the alia in the update statement instead of in a from statement.

Thanks for the help.

Answer 3

Here's one way, using your SELECT statement that works, and wrapping it in a CTE:

with cte as (
    select a.*, _regInfoID = (select b.id 
                        from table2 as b 
                        where b.ref1 = (select c.id 
                                        from table1 as c 
                                        where c.mainID = a.mainID 
                                              and sequence = 1))
    from table1 as a 
    )
update cte set regInfoID = _regInfoID

I like this style, since you can preview your modifications before applying them, and it's trivial to turn the SELECT into an UPDATE

But the problem you had with the original query was just a syntax error. This is how it should have been written. Note the FROM table1 AS a:

update a 
set regInfoID = (select b.id 
                 from table2 as b 
                 where b.ref1 = (select c.id 
                                 from table1 as c 
                                 where c.mainID = a.mainID 
                                       and sequence = 1))  
from table1 as a