| Author |
 Topic  |
|
|
deha_chopper
Starting Member
Indonesia
5 Posts |
 Posted - 12/05/2012 : 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
Indonesia
5 Posts |
Posted - 12/05/2012 : 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. |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/05/2012 : 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 |
Edited by - bandi on 12/05/2012 03:34:16 |
|
|
|
deha_chopper
Starting Member
Indonesia
5 Posts |
Posted - 12/05/2012 : 03:52:33
|
| thanks.. it works |
|
|
|
deha_chopper
Starting Member
Indonesia
5 Posts |
Posted - 12/05/2012 : 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". |
|
|
|
deha_chopper
Starting Member
Indonesia
5 Posts |
Posted - 12/05/2012 : 04:55:57
|
| ok.. i already figure it out |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/05/2012 : 05:32:04
|
quote:
Originally posted by deha_chopper
thanks.. it works
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
Updating Multiple Specific Rows
