Updating a clolumn in one table with data from another

[kgkane]

Hi all. I have an existing table (we'll call it table1)with one column with all null fields. Other columns in table1 have data in them already. I am trying to update the column of nulls with data from another table (table2) which also shares another column in common with table1. I've been trying to do with by running the following SQL query:
UPDATE table1 Set prod_size = (SELECT table2.prod_size FROM table2, table1 WHERE table1.IDnum = table2.IDnum)

This query gives me the following error:
Server: Msg 512, Level 16, State 1, Line 1
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.

Any suggestions/ideas? Thanks in advance for any help.

Kevin

 

[JamesLean]

UPDATE t1
SET prod_size = t2.prod_size
FROM t1 JOIN t2 ON t1.idnum = t2.idnum

--James

 

[PHV]

You may try this:
UPDATE table1 INNER JOIN table2 On table1.IDnum = table2.IDnum
Set table1.prod_size = table2.prod_size
WHERE table1.prod_size Is Null


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

would this work?

update table1 
   set prod_size 
     = ( select min(prod_size)
           from table2
          where idnum = table1.idnum )

all guesses are fair game as long as the poster didn't identify which particular dbms engine he's running



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[kgkane]

Thanks all for your responses. JamesLean, I tried what you suggested and it worked beautifully. Thanks. Everyone else, thanks again and maybe I'll be able to use your solutions in the future. Thanks all again!.


Kevin