SQL INSERT INTO Table A value from Table B based on Inner Join or where condition

Question

I need some help,

select 
    c.CityId, c.CityTitle, c.CountyID_fk, c.countytitle, 
    ct.CountyTitle, ct.CountyID 
from 
    tblCity C
inner join 
    tblCounty CT on ct.CountyTitle = c.CountyTitle

This is the output of this query to show two tables tblCity and tblCounty

I need to insert CountyIDs from tblCounty into the tblCity table for the column CountyID_fk.

I tried this:

insert into tblCity (CountyID_fk) 
    select 
        (select CountyID 
         from tblCounty C 
         inner join tblCity ct on ct.countytitle = c.CountyTitle)

But I get only this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any suggestions?

I tried the SQL query shown above, but I only get an error. I think I need to modify the query a bit.

Answer 1

insert into 
  tblCity (CountyID_fk) 
select 
  CountyID 
from 
  tblCounty C 
inner join 
  tblCity ct 
on 
  ct.countytitle = c.CountyTitle

Following on from Jeremys comment below...

The top part here:

insert into 
  tblCity (CountyID_fk) 

Is the standard T-SQL INSERT command, whereby you specify the Table you wish to insert into and a list of the attributes(column values) you would like to insert. This is a comma separated list of the form:

(<column 1>,<column 2>,...,<Column n>)

Where n = total columns you want to insert.

The next part is the part where you provide the data that you want to insert. In a standard INSERT command you would normally provide the keyword VALUES, followed by a list of tuples (rows), with each row matching the list of columns above. The So, in your example, a "standard" SQL INSERT would look like:

INSERT INTO 
  tblCity (CountyID_fk) 
VALUES
  (<CountryID_fk value>);

Now, because the INSERT command expects a set of data, i.e a number of rows to insert, the number of rows can be generated in a number of different ways.

In my suggested solution, the number of rows are generated by a separate SQL command, shown here:

    select 
      CountyID 
    from 
      tblCounty C 
    inner join 
      tblCity ct 
    on 
      ct.countytitle = c.CountyTitle

This SELECT statement will (inner) join the tables tblCountry and tblCity on the countrytitle attribute and will return a set of rows (probably just one) containing the CountyID (or an empty row if the (inner) join fails).

So, that is why you can combine the INSERT command with a SELECT to insert data into a table.

I hope this kind of explains my suggested solution.