MySQL – Update with Subquery in MariaDB 10.1.5

I have a list of users that I need to do an update against.

select sp.name from syndicate_store2.store_players as sp,
syndicate_ipb.nexus_purchases as np  
left join syndicate_ipb.members as mem  
on np.ps_member=mem.member_id where sp.authid = IntToSteam(mem.steamid)  
and np.ps_item_id=15;

this returns an expected list of names. Those named also exist in the db.table to be updated, sometimes multiple times. What i want to do is add a value to the current value each time the name occurs.

Lets say the select statements data returns this
john john john peter peter paul
so if i want to give 5000 credits to each person it would be something like credits = credits+5000, therefore i tried this:

update store_players set credits=credits+5000 
where name=(select sp.name from syndicate_store2.store_players as sp,
syndicate_ipb.nexus_purchases as np  
left join syndicate_ipb.members as mem  
on np.ps_member=mem.member_id  
where sp.authid = IntToSteam(mem.steamid)  
and np.ps_item_id=15);

we can ignore the inttosteam function, the gist should be relatively clear. My issue is of course the dreaded

    Table 'store_players' is specified twice, both as a target for 'UPDATE' 
and as a separate source for data

I can't figure out how to properly nest, join, or otherwise accomplish this task. any help is greatly appreciated! thanks!

per request here are the create tables:

| store_players | CREATE TABLE `store_players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `authid` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `credits` int(11) NOT NULL,
  `new_credits` int(11) NOT NULL DEFAULT '0',
  `date_of_join` int(11) NOT NULL,
  `date_of_last_join` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auth` (`authid`)
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

| members | CREATE TABLE `members` (
  `member_id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `member_group_id` smallint(3) NOT NULL DEFAULT '0',
  `email` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `joined` int(10) NOT NULL DEFAULT '0',
  `ip_address` varchar(46) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `posts` mediumint(7) DEFAULT '0',
  `title` varchar(1500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `allow_admin_mails` tinyint(1) DEFAULT NULL,
  `time_offset` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `skin` smallint(5) DEFAULT NULL,
  `warn_level` int(10) DEFAULT NULL,
  `warn_lastwarn` int(10) NOT NULL DEFAULT '0',
  `language` mediumint(4) DEFAULT NULL,
  `last_post` int(10) DEFAULT NULL,
  `restrict_post` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `view_sigs` tinyint(1) DEFAULT '1',
  `view_img` tinyint(1) DEFAULT '1',
  `bday_day` int(2) DEFAULT NULL,
  `bday_month` int(2) DEFAULT NULL,
  `bday_year` int(4) DEFAULT NULL,
  `msg_count_new` int(2) NOT NULL DEFAULT '0',
  `msg_count_total` int(3) NOT NULL DEFAULT '0',
  `msg_count_reset` int(1) NOT NULL DEFAULT '0',
  `msg_show_notification` int(1) NOT NULL DEFAULT '0',
  `misc` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_visit` int(10) DEFAULT '0',
  `last_activity` int(10) DEFAULT '0',
  `dst_in_use` tinyint(1) DEFAULT '0',
  `coppa_user` tinyint(1) DEFAULT '0',
  `mod_posts` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `auto_track` varchar(50) COLLATE utf8_unicode_ci DEFAULT '0',
  `temp_ban` varchar(100) COLLATE utf8_unicode_ci DEFAULT '0',
  `login_anonymous` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0&0',
  `ignored_users` mediumtext COLLATE utf8_unicode_ci,
  `mgroup_others` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `org_perm_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `member_login_key` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `member_login_key_expire` int(10) NOT NULL DEFAULT '0',
  `has_blog` mediumtext COLLATE utf8_unicode_ci,
  `blogs_recache` tinyint(1) DEFAULT NULL,
  `has_gallery` tinyint(1) NOT NULL DEFAULT '0',
  `members_auto_dst` tinyint(1) NOT NULL DEFAULT '1',
  `members_display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `members_seo_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `members_created_remote` tinyint(1) NOT NULL DEFAULT '0',
  `members_cache` longtext COLLATE utf8_unicode_ci,
  `members_disable_pm` int(1) NOT NULL DEFAULT '0',
  `members_l_display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `members_l_username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `failed_logins` mediumtext COLLATE utf8_unicode_ci,
  `failed_login_count` smallint(3) NOT NULL DEFAULT '0',
  `members_profile_views` int(10) unsigned NOT NULL DEFAULT '0',
  `members_pass_hash` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `members_pass_salt` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `member_banned` tinyint(1) NOT NULL DEFAULT '0',
  `member_uploader` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'default',
  `members_bitoptions` int(10) unsigned NOT NULL DEFAULT '0',
  `fb_uid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `fb_emailhash` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `fb_lastsync` int(10) NOT NULL DEFAULT '0',
  `members_day_posts` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0,0',
  `live_id` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `twitter_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `twitter_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `twitter_secret` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `notification_cnt` mediumint(9) NOT NULL DEFAULT '0',
  `tc_lastsync` int(10) NOT NULL DEFAULT '0',
  `fb_session` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `fb_token` mediumtext COLLATE utf8_unicode_ci,
  `ips_mobile_token` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `unacknowledged_warnings` tinyint(1) DEFAULT NULL,
  `conv_password` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sub_end` int(10) NOT NULL DEFAULT '0',
  `subs_pkg_chosen` smallint(3) NOT NULL DEFAULT '0',
  `cm_credits` float NOT NULL DEFAULT '0',
  `cm_reg` int(11) NOT NULL DEFAULT '0',
  `referred_by` int(11) NOT NULL DEFAULT '0',
  `cm_no_sev` tinyint(1) DEFAULT '0',
  `cim_profile_id` varchar(32) COLLATE utf8_unicode_ci DEFAULT '',
  `cim_payment_id` int(10) DEFAULT '0',
  `cim_method` int(5) NOT NULL DEFAULT '0',
  `cm_return_group` smallint(3) NOT NULL DEFAULT '0',
  `ipsconnect_id` int(10) NOT NULL DEFAULT '0',
  `ipsconnect_revalidate_url` mediumtext COLLATE utf8_unicode_ci,
  `shoutbox_shouts` bigint(8) NOT NULL DEFAULT '0',
  `warnLogs_tid` int(10) NOT NULL DEFAULT '0',
  `arcade_challengeid` mediumint(8) NOT NULL DEFAULT '0',
  `arcade_challenge` tinyint(1) NOT NULL DEFAULT '0',
  `arcade_gtype` int(11) NOT NULL DEFAULT '0',
  `arcade_downloads` tinyint(1) NOT NULL DEFAULT '0',
  `arcade_ban` tinyint(1) NOT NULL DEFAULT '0',
  `times_played` int(11) NOT NULL DEFAULT '0',
  `arcade_sess_gid` mediumint(8) NOT NULL DEFAULT '1',
  `arcade_sess_start` int(11) NOT NULL DEFAULT '0',
  `arcade_session` mediumint(8) NOT NULL DEFAULT '0',
  `arcade_time_spent` int(10) NOT NULL DEFAULT '0',
  `user_u_columns` tinyint(3) NOT NULL DEFAULT '1',
  `arcade_colamount` tinyint(4) NOT NULL DEFAULT '0',
  `arcade_columnsol` tinyint(1) NOT NULL DEFAULT '0',
  `arcade_postcount` tinyint(11) NOT NULL DEFAULT '0',
  `arcade_highscores` int(11) NOT NULL DEFAULT '0',
  `fav_games` mediumtext COLLATE utf8_unicode_ci,
  `arcade_b_challenges` mediumtext COLLATE utf8_unicode_ci,
  `is_arcade_mod` tinyint(1) NOT NULL DEFAULT '0',
  `arcade_chwon` int(11) NOT NULL DEFAULT '0',
  `arcade_u_cha` tinyint(3) NOT NULL DEFAULT '2',
  `arcade_challenge_banned` tinyint(1) NOT NULL DEFAULT '0',
  `arcade_lastplay` int(11) NOT NULL,
  `user_sort` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'name',
  `user_order` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ASC',
  `user_g_pp` mediumint(8) NOT NULL DEFAULT '20',
  `user_s_pp` mediumint(8) NOT NULL DEFAULT '5',
  `user_r_pp` mediumint(8) NOT NULL DEFAULT '5',
  `user_arcade_ng` mediumint(8) NOT NULL DEFAULT '0',
  `user_arcade_rg` smallint(8) NOT NULL DEFAULT '0',
  `user_arcade_pg` mediumint(8) NOT NULL DEFAULT '0',
  `user_arcade_ltsc` mediumint(8) NOT NULL DEFAULT '0',
  `points` mediumint(8) NOT NULL DEFAULT '1000',
  `user_arcade_nwch` mediumint(8) NOT NULL DEFAULT '0',
  `gow_wins` int(11) NOT NULL DEFAULT '0',
  `gow_points` int(11) NOT NULL DEFAULT '0',
  `gow_score` float NOT NULL DEFAULT '0',
  `gow_date` int(11) NOT NULL DEFAULT '0',
  `arcade_sess_gow` tinyint(1) NOT NULL DEFAULT '0',
  `arcade_spent` int(10) NOT NULL DEFAULT '0',
  `gname` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `steamid` varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
  `donate_donations` smallint(5) NOT NULL DEFAULT '0',
  `donate_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `inactive_notified` tinyint(1) NOT NULL DEFAULT '0',
  `inactive_lastNotified` int(10) NOT NULL DEFAULT '0',
  `inactive_oldGroup` smallint(3) NOT NULL DEFAULT '0',
  `inactive_moved` int(10) NOT NULL DEFAULT '0',
  `origfgrp` smallint(3) DEFAULT NULL,
  `origsbgrp` int(10) DEFAULT NULL,
  `SOServer` bit(7) DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  KEY `members_l_display_name` (`members_l_display_name`),
  KEY `members_l_username` (`members_l_username`),
  KEY `mgroup` (`member_group_id`,`member_id`),
  KEY `member_groups` (`member_group_id`,`mgroup_others`),
  KEY `bday_day` (`bday_day`),
  KEY `bday_month` (`bday_month`),
  KEY `member_banned` (`member_banned`),
  KEY `members_bitoptions` (`members_bitoptions`),
  KEY `ip_address` (`ip_address`),
  KEY `failed_login_count` (`failed_login_count`),
  KEY `joined` (`joined`),
  KEY `fb_uid` (`fb_uid`),
  KEY `twitter_id` (`twitter_id`),
  KEY `email` (`email`),
  KEY `blogs_recache` (`blogs_recache`)
) ENGINE=MyISAM AUTO_INCREMENT=49548 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |


| nexus_purchases | CREATE TABLE `nexus_purchases` (
  `ps_id` int(10) NOT NULL AUTO_INCREMENT,
  `ps_member` mediumint(8) NOT NULL DEFAULT '0',
  `ps_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ps_active` tinyint(1) NOT NULL DEFAULT '0',
  `ps_cancelled` tinyint(1) NOT NULL DEFAULT '0',
  `ps_start` int(10) NOT NULL DEFAULT '0',
  `ps_expire` int(10) NOT NULL DEFAULT '0',
  `ps_renewals` int(5) NOT NULL DEFAULT '0',
  `ps_renewal_price` float NOT NULL,
  `ps_renewal_unit` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `ps_app` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ps_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ps_item_id` int(10) NOT NULL DEFAULT '0',
  `ps_item_uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ps_admin_uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ps_custom_fields` mediumtext COLLATE utf8_unicode_ci,
  `ps_extra` mediumtext COLLATE utf8_unicode_ci,
  `ps_parent` int(10) NOT NULL DEFAULT '0',
  `ps_invoice_pending` int(10) NOT NULL DEFAULT '0',
  `ps_invoice_warning_sent` tinyint(1) NOT NULL DEFAULT '0',
  `ps_pay_to` mediumint(8) DEFAULT NULL,
  `ps_commission` int(2) DEFAULT NULL,
  `ps_original_invoice` int(10) NOT NULL DEFAULT '0',
  `ps_tax` int(5) NOT NULL DEFAULT '0',
  `ps_can_reactivate` tinyint(1) NOT NULL DEFAULT '0',
  `ps_grouped_renewals` mediumtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`ps_id`),
  KEY `ps_member` (`ps_member`),
  KEY `sort` (`ps_cancelled`,`ps_active`,`ps_start`)
) ENGINE=MyISAM AUTO_INCREMENT=588 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

select sp.name from syndicate_store2.store_players as sp, syndicate_ipb.nexus_purchases as np  left join syndicate_ipb.members as mem  on np.ps_member=mem.member_id  where sp.authid = IntToSteam(mem.steamid)  and np.ps_item_id=15;
+----------------+
| name           |
+----------------+
| fontaine       |
| fontaine       |
| kscx12         |
| Jaaay          |
| nanoc          |
| Rune           |
| SaltedPotatoes |
| Mikasa         |
| Triv           |
| RainbowDashy   |
| Amon           |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| ctark          |
| mesdimo        |
| percynezz      |
+----------------+
27 rows in set (0.00 sec)

Best Answer

As the MySQL documentation mentions at the bottom of the UPDATE documentation:

Currently, you cannot update a table and select from the same table in a subquery.

I've modified your query and I added another subquery:

update store_players as sp 
set sp.credits=sp.credits+5000 
where sp.name IN (
    select a.name from (select sp2.name from syndicate_store2.store_players as sp2,
    syndicate_ipb.nexus_purchases as np  
    left join syndicate_ipb.members as mem  
    on np.ps_member=mem.member_id  
    where sp2.authid = IntToSteam(mem.steamid)  
    and np.ps_item_id=15) AS a);

And without subqueries:

UPDATE test.store_players as sp 
JOIN syndicate_ipb.nexus_purchases AS np ON (np.ps_item_id=15)
LEFT JOIN syndicate_ipb.members as mem on (np.ps_member=mem.member_id)
SET sp.credits=sp.credits+5000 
WHERE sp.authid = IntToSteam(mem.steamid);

EDIT:

Query:

update store_players as sp 
JOIN (select sp2.name,COUNT(sp2.name) AS cnt 
        from syndicate_store2.store_players as sp2,
        syndicate_ipb.nexus_purchases as np  
        left join syndicate_ipb.members as mem  
        on np.ps_member=mem.member_id  
        where sp2.authid = IntToSteam(mem.steamid)  
        and np.ps_item_id=15
        group by sp2.name) AS q
    ON (q.name=sp.name)
set sp.credits=sp.credits+(5000*q.cnt);

PD: Replace the schema test for your store_players table schema.

Try it!

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Are two indexes needed

Related Question