Update a column in a table using the values in another table

[ak1dnar]

There are two tables in my database
1. Table_A
2. Table_B


Structure for Table_A

 A_ID - A_code(FK to B_ID) 
1001 - Null
1002 - Null
1003 - Null
 

Structure for Table_B

 B_ID - B_A_ID (FK to A_ID) 
501 - 1001 
502 - 1003 
503 - 1002 
 

I need to update A_code column with B_ID with the according to the relation ship.this might be work for single recored.

 
UPDATE Table_A SET A_code = (select 
Table_B.B_ID
from Table_B
Inner join Table_A on 
Table_A.A_ID=Table_B.B_A_ID 
Where Table_B.B_A_ID = 1001)
Where A_ID = 1001

Then how to update column A_code at once?

[hariharanmca]

There are two tables in my database
1. Table_A
2. Table_B


Structure for Table_A

 A_ID - A_code(FK to B_ID) 
1001 - Null
1002 - Null
1003 - Null
 

Structure for Table_B

 B_ID - B_A_ID (FK to A_ID) 
501 - 1001 
502 - 1003 
503 - 1002 
 

I need to update A_code column with B_ID with the according to the relation ship.this might be work for single recored.

 
UPDATE Table_A SET A_code = (select 
Table_B.B_ID
from Table_B
Inner join Table_A on 
Table_A.A_ID=Table_B.B_A_ID 
Where Table_B.B_A_ID = 1001)
Where A_ID = 1001

Then how to update column A_code at once?

UPDATE Table1 INNER JOIN Table2 ON Table1.A_ID = Table2.B_A_ID SET Table1.A_COde = [Table2].[B_ID]

concider Table_A as Table1
Table_B as Table2

i think this will help you

[hariharanmca]

UPDATE Table1 INNER JOIN Table2 ON Table1.A_ID = Table2.B_A_ID SET Table1.A_COde = [Table2].[B_ID]

concider Table_A as Table1
Table_B as Table2

i think this will help you

UPDATE Table1 INNER JOIN Table2 ON Table1.A_ID = Table2.B_A_ID SET Table1.A_COde = Table2.B_ID
WHERE (((Table1.A_ID) Like '1001'))

this will work for 1 record, which is 1001

if Table1.A_ID is text type then WHERE (((Table1.A_ID) Like '1001'))
if Table1.A_ID is Number type then WHERE (((Table1.A_ID) = 1001))

[ak1dnar]

I create a sample table with this Queries.But failed when try to run the update.

 
Create Table Table_1(
ID int primary key Identity (1001,1),
Code varchar(10) null 
)
Go
 
insert into Table_1(Code)values(null)
insert into Table_1(Code)values(null)
insert into Table_1(Code)values(null)
 
Go 
Create Table Table_2(
ID int primary key Identity (501,1),
FK_ID int not null,
FOREIGN KEY (FK_ID) REFERENCES Table_1(Id)
)
Go
insert into table_2(FK_ID)values(1001)
insert into table_2(FK_ID)values(1002)
insert into table_2(FK_ID)values(1003)
Go
select * from table_1
select * from table_2
 
 

Update Query

 
 
 
UPDATE Table_1 INNER JOIN Table_2 
ON Table_1.ID = Table_2.FK_ID 
SET Table_1.Code = Table_2.ID
 

Please try it and help me to solve this.
Thanks a Lot!

[vijaii]

I create a sample table with this Queries.But failed when try to run the update.

 
Create Table Table_1(
ID int primary key Identity (1001,1),
Code varchar(10) null 
)
Go
 
insert into Table_1(Code)values(null)
insert into Table_1(Code)values(null)
insert into Table_1(Code)values(null)
 
Go 
Create Table Table_2(
ID int primary key Identity (501,1),
FK_ID int not null,
FOREIGN KEY (FK_ID) REFERENCES Table_1(Id)
)
Go
insert into table_2(FK_ID)values(1001)
insert into table_2(FK_ID)values(1002)
insert into table_2(FK_ID)values(1003)
Go
select * from table_1
select * from table_2
 
 

Update Query

 
 
 
UPDATE Table_1 INNER JOIN Table_2 
ON Table_1.ID = Table_2.FK_ID 
SET Table_1.Code = Table_2.ID
 

Please try it and help me to solve this.
Thanks a Lot!

Try this ...

UPDATE TABLE_A
SET A_CODE = B_ID
FROM TABLE_A INNER JOIN TABLE_B
ON A_ID = B_A_ID

[ak1dnar]

Try this ...

UPDATE TABLE_A
SET A_CODE = B_ID
FROM TABLE_A INNER JOIN TABLE_B
ON A_ID = B_A_ID

Does this one do the same thing as your one?
(see my previous post for structure of tables)

UPDATE table_1
SET Code = table_2.ID
FROM TABLE_2
WHERE TABLE_1.ID = TABLE_2.FK_ID

Or Any issues with this?

[vijaii]

Does this one do the same thing as your one?
(see my previous post for structure of tables)

UPDATE table_1
SET Code = table_2.ID
FROM TABLE_2
WHERE TABLE_1.ID = TABLE_2.FK_ID

Or Any issues with this?

You can do it in this way also.

[ak1dnar]

You can do it in this way also.

Many Thanks! then no need to struggle with Joins.