0

I have a huge table with items

item-id | name | type | extra

Many of them are "old versions" of type 0.

I have to update the extra field in all new items (same item-id, but type > 0) with the extra data we have for the old version.

Since this is a very large table with millions of rows, I have to be very careful with joining the table with itself.

Basically I want to do this

update mytable t1 set extra = (select extra from mytable where t1.itemid = mytable.itemid and type=0) where type > 0;

But MySQL doesn't accept t1 in the sub-select.

1

Recently read an article that might help you, see: https://dzone.com/articles/specifying-target-tables-in-mysql

Still think a self join would be faster than a correlated sub-query. Especially on MySQL 5.5 and less, where these type of sub queries are badly optimized.

Self join like:

UPDATE mytable t1
  JOIN mytable t2
    ON t1.item-id = t2.item-id
SET t1.extra = t2.extra
WHERE t1.type > 0;
  • Sorry for the bad formatting. I was on my phone when I wrote the answer. – naschoff Oct 18 '15 at 9:14
0

I would do a "bulk select/insert into" script using cursors, since you mentioned it's a pretty big table. I'd also set the commit frequency between 20-50k records with rollback logic.

Do you have experience creating these types of update scripts?

  • How would that look like with MySQL ? – Gene Vincent Oct 18 '15 at 3:11
-2

Your syntax is wrong Try this:

Update TableName
Set columnName = Value
From TableName T1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.