How to restore 3 columns from backup_smf_messages ?

Started by fiver, February 24, 2011, 01:18:14 AM

Previous topic - Next topic

fiver

For SMF 2 RC5


Hi,


I have tried in the last few days to restore 3 columns (X, Y and Z) from backup_smf_messages  to smf_messages . It seems to put the data back into the wrong rows. May I know how do I put them back in the correct rows?

In smf_messages there are 3 columns X Y and Z where all the values are missing (DROP by accident).

id_msgXYZ
10000
10100
10200
10300
10400

In backup_smf_messages there are 3 columns with all the correct values:

id_msgXYZ
1004270
10110
10210
10330abc
10420

This is what I used:
REPLACE INTO smf_messages SELECT * FROM backup_smf_messages  WHERE X = 0;
REPLACE INTO smf_messages SELECT * FROM backup_smf_messages  WHERE X = 1;
REPLACE INTO smf_messages SELECT * FROM backup_smf_messages  WHERE X = 2;
REPLACE INTO smf_messages SELECT * FROM backup_smf_messages  WHERE X = 3;
REPLACE INTO smf_messages SELECT * FROM backup_smf_messages  WHERE X = 4;
The value was restored but into the wrong rows.

I am now thinking of using UPDATE instead of REPLACE, but I can't seems to get it:

UPDTAE smf_messages SET X = X WHERE backup_smf_messages.id_msg = smf_messages.id_msg
UPDTAE smf_messages SET Y = Y WHERE backup_smf_messages.id_msg = smf_messages.id_msg
UPDTAE smf_messages SET Z = Z WHERE backup_smf_messages.id_msg = smf_messages.id_msg

Is the above the correct way of writing it so that it will restore into the correct rows (base on id_msg)? Thanks.

fiver

Thought if all fields are the same in rows, perhaps I need to restore by rows instead.

In smf_messages:

id_msgXYZ
10000
10100
10200
10300
10400
10500
10600
10700

In backup_smf_messages:

id_msgXYZ
1004270
10110
10210
10330abc
10420


If backup_smf_messages have less rows than smf_messages, how do I restore rows without touching the lastest rows 105 106 and 107?

Arantor

UPDATE smf_messages
SET smf_messages.X = backup_smf_messages.X,
smf_messages.Y = backup_smf_messages.Y,
smf_messages.Z = backup_smf_messages.Z
WHERE backup_smf_messages.id_msg = smf_messages.id_msg


This will work fine on older rows, since newer rows won't have a matching value in backup_smf_messages.

fiver

#3
Thanks Arantor,


I got an error message:


#1054 - Unknown column 'backup_smf_messages.id_msg' in 'where clause'

I checked and backup_smf_messages.id_msg is there.

Arantor

Oh, I forgot how MySQL needs to have multi-table updates.

UPDATE smf_messages, backup_smf_messages
SET smf_messages.X = backup_smf_messages.X,
smf_messages.Y = backup_smf_messages.Y,
smf_messages.Z = backup_smf_messages.Z
WHERE backup_smf_messages.id_msg = smf_messages.id_msg

fiver

#5
Many thanks. It worked!


I spent half a day googling and reading "#1054 - Unknown column" and was getting no where.
I'm wondering, is it true in mySQL that there is no way to replace a column and the only way is to replace a row?

Arantor

It depends what you're trying to do.

MySQL is row based, which means if you change a column, you have to rewrite every row in the table with the new structure. REPLACE is to add new rows, not new columns.

fiver

Thanks for the reply.


I learned a few things through this troubleshooting. :D

Advertisement: