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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Updating Multiple Specific Rows

Author  Topic 

deha_chopper
Starting Member

5 Posts

Posted - 2012-12-05 : 03:11:53
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

5 Posts

Posted - 2012-12-05 : 03:26:04
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 03:27:47
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
Go to Top of Page

deha_chopper
Starting Member

5 Posts

Posted - 2012-12-05 : 03:52:33
thanks.. it works
Go to Top of Page

deha_chopper
Starting Member

5 Posts

Posted - 2012-12-05 : 04:02:17
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

5 Posts

Posted - 2012-12-05 : 04:55:57
ok.. i already figure it out
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 05:32:04
quote:
Originally posted by deha_chopper

thanks.. it works


Welcome

--
Chandu
Go to Top of Page
   

- Advertisement -