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.
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...
INSERT INTO categories
(catName)
(SELECT DISTINCT coName FROM counties)
