Insert into Column from another Table's Column

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

Insert into Column from another Table's Column

Author

Topic

raj_sree216
Starting Member

3 Posts

Posted - 2014-11-27 : 05:47:27

Hi SQL Gurus,

I came from DW admin background and new to SQL programming.
I have 3 tables:
CUSTOMER - cust_id, cust_name, cust_state
SALES_HEADER - sales_id, cust_id, sales_amount
SALES_DETAIL - prod_id, sales_id, prod_name, prod_price

I am trying to insert into SALES_HEADER table from CUSTOMER and SALES_DETAIL tables. Here are the SQL queries that I am trying to insert.

insert into dbo.SALES_HEADER (SALES_ID,SALES_AMOUNT)
select SALES_ID, sum(PROD_PRICE)
from BOBJ.dbo.SALES_DETAIL
group by SALES_ID;

The above query is working good & to get cust_id from CUSTOMER:

Update dbo.SALES_HEADER
set CUST_ID = (select CUST_ID from dbo.CUSTOMER
where dbo.SALES_HEADER.CUST_ID = dbo.CUSTOMER.CUST_ID)
where CUST_ID is null;

This query is not working & getting message - 6 rows updated successfully.
But using select * from dbo.SALES_HEADER, CUST_ID is still showing as NULL.
Can anyone please tell me what did I do wrong and is there a way to write single query rather than two queries.

Thanks in Advance
Sree raj

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-11-27 : 06:13:13

This Should work...

UPDATE SH
SET SH.CUST_ID=C.CUST_ID
FROM dbo.SALES_HEADER SH
INNER JOIN dbo.CUSTOMER C
ON SH.CUST_ID =C.CUST_ID
WHERE SH.CUST_ID IS NULL

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

raj_sree216
Starting Member

3 Posts

Posted - 2014-11-27 : 08:51:12

quote:
Originally posted by MuralikrishnaVeera

This Should work...

UPDATE SH
SET SH.CUST_ID=C.CUST_ID
FROM dbo.SALES_HEADER SH
INNER JOIN dbo.CUSTOMER C
ON SH.CUST_ID =C.CUST_ID
WHERE SH.CUST_ID IS NULL

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

raj_sree216
Starting Member

3 Posts

Posted - 2014-11-27 : 08:53:41

I tried the query you mentioned. But it is saying 0 rows affected.
Any other suggestions please.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources