2

I'm a complete newcomer to this kind of thing, hence the simple question, but I have 2 copies of a database table which I need to combine somewhat.

Both have the exact same structure and contain member account usernames and details. In one column in table_1 - screen_name, the data has been truncated, so I need to replace this with the data in the same column in table_2, which is an older backup of the same table. I'm going to repeat for a few columns, but first, I'm trying to replace the truncated screen_name.

I'm guessing it should be pretty simple, but I just can't get it to work. There are only about 4000 rows - the only potential complication is that table_1 has more records than table_2 (it's a newer version, and more members have registered since then), so the column itself can't be completely replaced by the older backup.

Here's what I've tried, but it doesn't seem to be working (getting a syntax error, apparently):

UPDATE exp_memberscorrupt 
SET exp_memberscorrupt.screen_name = exp_membersclean.screen_name 
JOIN exp_membersclean
JOIN exp_memberscorrupt on exp_membersclean.member_id = exp_memberscorrupt.member_id

Any ideas at all?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
jontly
  • 238
  • 1
  • 4
  • 12

3 Answers3

2

If this is SQL Server, the syntax is

UPDATE exp_memberscorrupt 
SET exp_memberscorrupt.screen_name = exp_membersclean.screen_name 
FROM exp_membersclean
JOIN exp_memberscorrupt on exp_membersclean.member_id = exp_memberscorrupt.member_id

Since it is MySQL, the syntax for update-from-join is: (you move the tables to the top after "UPDATE")

UPDATE exp_membersclean
JOIN exp_memberscorrupt on exp_membersclean.member_id = exp_memberscorrupt.member_id
SET exp_memberscorrupt.screen_name = exp_membersclean.screen_name
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Hmm, I'm still picking up syntax errors here - MAMP on OSX, but I guess that shouldn't be a problem? #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM exp_membersclean JOIN exp_memberscorrupt on exp_membersclean.member_id = e' at line 3 – jontly Apr 07 '11 at 00:20
  • Didn't realise it was MySQL, since your syntax was closer to SQL Server. – RichardTheKiwi Apr 07 '11 at 00:34
  • Ah, my mistake. That works perfectly - I feel a little more enlightened now ;) thanks! – jontly Apr 07 '11 at 01:01
0
update exp_memberscorrupt cor
set cor.screen_name  = cln.screen_name
from exp_membersclean cln
where cor.member_id = cln.screen_name
Greg Randall
  • 811
  • 7
  • 14
0

Try this,

dont forget to use WHERE clause, and mysql_error() function for capturing db errors

UPDATE exp_memberscorrupt
INNER JOIN exp_membersclean
ON exp_membersclean.member_id = exp_memberscorrupt.member_id
SET exp_memberscorrupt.screen_name = exp_membersclean.screen_name
WHERE ...

muratgozel
  • 2,333
  • 27
  • 31