Help? Insert/Update Table fields x,y,z where a = a

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

General SQL Server Forums

New to SQL Server Programming

Help? Insert/Update Table fields x,y,z where a = a

Author

Topic

jimconnors
Starting Member

1 Post

Posted - 2012-06-27 : 18:40:41

Hopefully either my subject was cryptic enough to elicit a "whiskey tango foxtrot?!" and a click or you understand where I was going. Either way, here is my challenge.

I have two tables. Table1 has all the data with an ID field, Table2 has the same ID field populated, and a few blank columns.
I want to pull the related columns from Table1 into Table2 based on the 1 to 1 relationship of the ID field.

what I had put together but I keep getting an error on is below:


Insert into Table2 (Table2.Category, Table2.Type, Table2.Item, Table2.Symptom, Table2.Abstract)
select Table1.Category, Table1.Type, Table1.Item, Table1.Symptom, Table1.Abstract from Table1
where cast(cast(Table1.[Request ID] as varchar(15)) as int) = Table2.Request_ID

I keep getting an error that the last field Request_ID is an invalid column name.

I've worked in Oracle before, and MSSQL but its been about 4-5 years, so there is plenty of rust. This environment is MSSQL 2000.

Thanks in advance,

J

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-06-27 : 18:52:20

Your select statement is referencing a column for a table, Table2, that is not in your query.

You will need reference Table2 by a join or subquery. I'm not sure what you are trying to do.

It actually sounds like you are trying to update a row in Table2, but you can't do that with an insert statement. You would need to do an update for that.

CODO ERGO SUM

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-27 : 18:55:43

something like:


update table1
   set table1.cateory=table2.cateory
      ,table1.type=table2.type
      ,table1.item=table2.item
      ,table1.symptom=table2.symptom
      ,table1.abstract=table2.abstract
  from table1
       inner join table2
               on table1.request_id=table2.request_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources