[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Help with SQL command

I have two tables:

table_1
table_2

table_1 has the following columns
b_id            - Primary Key
company
contact

table_2 has the following columns
sales_id     -Primary Key
company
contact
b_id           -currently null

I would like to create a sql query that will match the company from table_1 with company from table_2 and grab b_id from table_1 and put it in table_2 for b_id

Is this possible

0
digarati
Asked:
digarati
  • 3
  • 2
  • 2
  • +2
5 Solutions
 
pzozulkaCommented:
Update table2
Set table2.b_id = table1.b_id
Where Table1.company = table2.company
0
 
sm394Commented:
or

UPDATE t2
SET t2. b_id= t1.b_id
FROM table_2  t2  INNER JOIN table_1 t1
ON t2.company = t1.company
0
 
SharathData EngineerCommented:
UPDATE table_2
SET b_id= t1.b_id
FROM table_2  t2  INNER JOIN table_1 t1
ON t2.company = t1.company
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
sujith80Commented:
Use this update stmt
update table_2 a
set b_Id = (select b.b_id
            from table_1 b
	    where b.company = a.company)
where exists (select 1
            from table_1 b
	    where b.company = a.company);

Open in new window

0
 
Mariner73Commented:
You can use upper for company, because they could be entered diferently

update table_2 a

set
 b_Id 
=
 
(
select
 b
.
b_id
            
from
 table_1 b
            
where
 
upper(b
.
company) 
=
 Upper(a
.
company
)
	    )


where
 exists 
(
select
 
1

            
from
 table_1 b
            
where 

upper(b
.
company)
 
=
 upper(a
.
company)

            );

Open in new window

0
 
sujith80Commented:
>> You can use upper for company

But this could be a performance penalty. A function on the join columns prevent the possible usage of indexes on those columns.
Generally a well designed application populate data in a single case.
0
 
sujith80Commented:
digarati,
can you explain how this "accepted" solution works for you?
It is not even complete in syntax
0
 
SharathData EngineerCommented:
I agree with Sujith's comments. Your accepted solution wont work for you.
0
 
Mariner73Commented:
To sujith80, generally I agree,
upper() is not best for performance, But it is obvious there is some inconsistency in data,
once he need to populate key column, so, application provided this data could be different.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now