MySQL questions

[Carlos Camacho]

Greetings,

It seems like it has taken me forever to get all our content in one database/CMS (errr. it has, he he). I have a few questions for some of you MySQL gurus...

I hae a table called exp_weblog_titles, it contains a field called url_title, and I want to copy (for all records) into the field field_id_5 of table called exp_weblog_data.

I used this SQL command, and it said it succeeded but the data doesn't seem to have been copied.

INSERT INTO `exp_weblog_data` (field_id_5) SELECT url_title FROM `exp_weblog_titles`;

I'll ask my other questions once I have the above set.

Thanks,

[Carlos Camacho]

I just looked at my able with PhpMySQL and saw that before Ihad 500 records and now have 1000. Looks kike my copying didn't UDPATE the exisitin records, but rather creaed new ones with JUST that field. lol

Help!

[wyrmmage]

I'm assuming there's some sort of id system on the table?

for($i=0; $i<=$howeverManyIdsYouHave; $i++)
{
$result = mysql_query("SELECT url_title FROM `exp_weblog_titles` WHERE id=" . $i . ");
mysql_query("UPDATE `exp_weblog_data` SET field_id_5=" . $result . " WHERE id=" . $i . ");
}

Kind of depends on how the tables are set up, but thats the general idea :)
-wyrmmage

[Carlos Camacho]

Do I really need to do this with PHP code? Can't I just run a SQL command and copy in one fell swoop?

Thanks,

[AndyKorth]

UDPATE `exp_weblog_data` SET field_id_5 = (SELECT url_title FROM `exp_weblog_titles`);

Source:
http://en.wikipedia.org/wiki/Update_(SQL)

(You might have to experiment with the ticks, I don't use MySQL at work)

[PowerMacX]

Without knowing the primary key of either table, its hard to suggest anything...

[Carlos Camacho]

Primary key is the same and is called entry_id

[PowerMacX]

update exp_weblog_data, exp_weblog_titles
set exp_weblog_data.field_id_5 = exp_weblog_titles.url_title
where exp_weblog_data.entry_id = exp_weblog_titles.entry_id

Actually, I'd also need to know the foreign key(s) between those to tables to make sure that update makes sense (is the entry_id in both tables referring to the same thing?)

[Carlos Camacho]

the entry_id in both tables referring to the same thing?)
Yes it is.

I used your command and it worked!!!! One step closer to getting iDG's content back online!