1

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)
  • Can you add your CREATE TABLE statements? – oNare Jul 14 '15 at 1:22
  • it's going to be a lot of data, but sure! added now! – driz Jul 14 '15 at 1:26
1

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!

  • ERROR 1242 (21000): Subquery returns more than 1 row This makes sense since the subquery could return hundreds of rows to be operated on the second query appears to have worked!! will evaluate data! – driz Jul 14 '15 at 1:55
  • I updated the answer, did you try it with the IN? – oNare Jul 14 '15 at 1:57
  • ill try the first with the update, the second worked perfect. I'm accepting your answer based on the second. – driz Jul 14 '15 at 1:58
  • actually, while it did achieve the primary goal, the credits didn't append. I see your code has sp.credits=sp.credits+5000, in one case a specific user should have cycled through 14 times to have sp.credits=70000 but he only has 5000, any ideas? – driz Jul 14 '15 at 2:05
  • 1
    Can you add to your question some sample data to test one new query that should do the trick? I need it from the 3 tables. – oNare Jul 14 '15 at 2:49

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.