Join tables together in sql

Question

0.00/5 (No votes)

See more:

Hi,

I am trying to write a query to match a col "bus_name" in two table and if it matches copy the revenue from table 2 to table1.
Table 1 : (-- means space)
Bus_id--bus_name--Bus_address--revenue
1010--Tata motr--Coll north-- NULL
1020--Celeriac--Celeriac new-- NULL
1030--Tagore--Tagore south-- NULL
1040--Nehru--Nehru King-- NULL

Table 2 :
New_id--bus_name--Bus_address--revenue
20--Class new--westgate new-- 75050
10--Tata motr--north-- 10050
30--Glass--Glass-- 80900
40--Tagore--Tagore south-- 25000

Resulting table t1 should be:
Bus_id--bus_name--Bus_address--revenue
1010--Tata motr--Coll north-- 10050
1020--Celeriac--Celeriac new-- NULL
1030--Tagore--Tagore south-- 25000
1040--Nehru--Nehru King-- NULL

I have written query as below but it doesnt work :

Update t1
set t1.Revenue = t2.revenue
when (
select * from t1
Inner Join t2
on t1.r1_bus_name_full = t2.r1_bus_name_full ) ;

Pls help !!

Posted 27-Jan-16 13:23pm

Member 12271876

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mathi Mani · Accepted Answer · 2016-01-27T13:46:00

Please change your query like this and try:

C#

update a set a.revenue = b.revenue
from table1 a inner join table2 b 
on a.busname = b.busname

You can also use MERGE to achieve the same:

C#

merge table1 as a
using table2 as b
on (a.busname = b.busname)
when matched
	then update set a.revenue = b.revenue;

Note: Column names may not match, but you get the idea.