Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2241 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
2241 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  
 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.05 seconds. Powered By: Snitz Forums 2000