SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Updating Multiple Specific Rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deha_chopper
Starting Member

Indonesia
5 Posts

Posted - 12/05/2012 :  03:11:53  Show Profile  Reply with Quote
Hi all,

Need some help here,
I want to update one table based on another table.
For example, I have 3 tables
TABLE A
NAME REGION IS_A QUANTITY
Andi INN N 0
Andi OUT N 0
Andi POR N 0

TABLE B
REGION QUANTITY
INN 5
OUT 3
POR 4

TABLE C
NAME REG IS_A
Andi INN A

So, what i want to do is to update table A based on table B and C.
Since there is only Andi in table C, and Andi's REGION is INN, I want to update table A for Andi to be like below and keep the others value.
TABLE A
NAME REGION IS_A QUANTITY
Andi INN A 5
Budi OUT N 0
Charlie POR N 0

i'm sorry if there is any unclear explanation.

deha_chopper
Starting Member

Indonesia
5 Posts

Posted - 12/05/2012 :  03:26:04  Show Profile  Reply with Quote
Hi all,

Need some help here,
I want to update one table based on another table.
For example, I have 3 tables
TABLE A
NAME REGION IS_A QUANTITY
Andi INN N 0
Andi OUT N 0
Andi POR N 0

TABLE B
REGION QUANTITY
INN 5
OUT 3
POR 4

TABLE C
NAME REG IS_A
Andi INN A
Budi OUT A

So, what i want to do is to update table A based on table B and C.
Since there is only Andi and Budi in table C, and Andi's REGION is INN and Budi's REGION is OUT, I want to update table A for Andi and Budi to be like below and keep the others value.
TABLE A
NAME REGION IS_A QUANTITY
Andi INN A 5
Budi OUT A 3
Charlie POR N 0

just for information, i have a lot of different names and regions
i'm sorry if there is any unclear explanation.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/05/2012 :  03:27:47  Show Profile  Reply with Quote
UPDATE a
SET IS_A = c.IS_A ,QUANTITY = b.quantity, NAME = c.NAME
FROM TABLEA a JOIN TABLEB b ON a.REGION= b.REGION
JOIN TABLEC c ON a.NAME = c.NAME AND b.REGION= c.REG


EDIT:
DECLARE @TABLEA TABLE(NAME varchar(10), REGION VARCHAR(10), IS_A CHAR(1), QUANTITY int)
INSERT INTO @TABLEA
SELECT 'Andi', 'INN', 'N', 0 union all
SELECT 'Andi', 'OUT', 'N', 0 union all
SELECT 'Andi', 'POR', 'N', 0

DECLARE @TABLEB TABLE(REGION VARCHAR(10), QUANTITY int)
INSERT INTO @TABLEB
SELECT 'INN', 5 union all
SELECT 'OUT', 3 union all
SELECT 'POR', 4

DECLARE @TABLEC TABLE(NAME varchar(10), REG VARCHAR(10), IS_A CHAR(1))
INSERT INTO @TABLEC
SELECT 'Andi', 'INN', 'A' union all
SELECT 'Budi', 'OUT', 'A'

UPDATE @TABLEA
SET IS_A = c.IS_A ,QUANTITY = b.quantity, NAME = c.NAME
FROM @TABLEA a JOIN @TABLEB b ON a.REGION= b.REGION
JOIN @TABLEC c ON b.REGION= c.REG

SELECT * FROM @TABLEA
--
Chandu

Edited by - bandi on 12/05/2012 03:34:16
Go to Top of Page

deha_chopper
Starting Member

Indonesia
5 Posts

Posted - 12/05/2012 :  03:52:33  Show Profile  Reply with Quote
thanks.. it works
Go to Top of Page

deha_chopper
Starting Member

Indonesia
5 Posts

Posted - 12/05/2012 :  04:02:17  Show Profile  Reply with Quote
can i update the table in database based on the temp table?
i try to update but i comes error
Msg 137, Level 16, State 1, Line 27
Must declare the scalar variable "@TABLEA".
Go to Top of Page

deha_chopper
Starting Member

Indonesia
5 Posts

Posted - 12/05/2012 :  04:55:57  Show Profile  Reply with Quote
ok.. i already figure it out
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/05/2012 :  05:32:04  Show Profile  Reply with Quote
quote:
Originally posted by deha_chopper

thanks.. it works


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000