CODEWITHSUNDEEP
sql
Mark
Mark

Reputation: 1872

SQL - Insert into table data from another

I have an issue, I want to take the value of the field 'coName' in my counties table and insert it into the 'catName' field of my categories table.

Problem is, there's 178 rows in the counties table so the following SQL doesn't quite work:

INSERT INTO categories
SET catName=
(
    SELECT coName
    FROM counties 
    WHERE coCountryId=201
)

Anybody know a way I can run this query 178 times and not duplicate the entries inserted.

Upvotes: 1

Views: 75

Answers (2)

logixologist
logixologist

Reputation: 3834

You can do it like this

INSERT INTO categories (coname)
    SELECT DISTINCT coName
    FROM counties

Unless you are trying to do an update, that would be different. One important thing to note is: When you do INSERT INTO and SELECT you have to make sure you explicitly state each column and there needs to be a matching number of columns in the SELECT and the INSERT. So in this case I assumed your field in categories was coname...

Upvotes: 3

Mureinik
Mureinik

Reputation: 312289

INSERT INTO categories
(catName)
(SELECT DISTINCT coName FROM counties)

Upvotes: 2

Related Questions