Updating Multiple Specific Rows

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.

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

deha_chopper
Starting Member

5 Posts

Posted - 2012-12-05 : 03:52:33

thanks.. it works

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".

deha_chopper
Starting Member

5 Posts

Posted - 2012-12-05 : 04:55:57

ok.. i already figure it out

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 05:32:04