I have an SQL table called media that has a string feild called 'creator'.
I have a new SQL table called author that has a stirng feild called 'author_name'
I added a column to 'media' called author_id.
How do I select distinct authors from media and put each of them into author_name, and then link the author_id to the author table, all in pure SQL?
Name:
Anonymous2006-08-23 13:32
1/2 nevermind:
INSERT INTO authors( author_name )
SELECT DISTINCT `creator`
FROM `media`
Name:
Anonymous2006-08-23 14:03
2/5ths nevermind, this gets me close
SELECT *
FROM media AS m
LEFT JOIN articles AS a ON m.type_id = a.article_id
LEFT JOIN authors ON authors.author_name = m.creator
WHERE m.type =1
LIMIT 0 , 30
Name:
Anonymous2006-08-23 14:32
Step 1:
INSERT INTO authors( author_name )
SELECT DISTINCT `creator`
FROM `media`
Step 2:
CREATE TABLE temp SELECT article_id, author_id
FROM media AS m
LEFT JOIN articles AS a ON m.type_id = a.article_id
LEFT JOIN authors ON authors.author_name = m.creator
WHERE m.type =1
Name:
Anonymous2006-08-23 14:36
Step 3
UPDATE articles AS a,
temp AS t SET a.author_id = t.author_id WHERE a.article_id = t.article_id
Name:
Anonymous2006-08-23 14:52
Final SQL.
ALTER TABLE `articles` ADD `author_id` INT NOT NULL;
INSERT INTO authors( author_name )
SELECT DISTINCT `creator`
FROM `media`;
CREATE TABLE temp SELECT article_id, author_id
FROM media AS m
LEFT JOIN articles AS a ON m.type_id = a.article_id
LEFT JOIN authors ON authors.author_name = m.creator
WHERE m.type = 1;
UPDATE articles AS a,
temp AS t SET a.author_id = t.author_id WHERE a.article_id = t.article_id;
Bringing /prog/ back to its people
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy