StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
19663558
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2013-10-29T16:09:03.693
FavoriteCount
0
LastActivityDate
2013-10-29T16:09:03.693
LastEditDate
LastEditorUserId
0
OwnerUserId
1048425
ParentId
19661606
PostTypeId
2
Score
1
ViewCount
0
LastEditorDisplayName
text
Body
You are missing the <code>CREATE</code> from the start of your stored procedure, by adding this I was able to get your procedure working on <a href="http://sqlfiddle.com/#!2/8588b/1" rel="nofollow">SQL Fiddle</a> (Note the 4th result set has one row of paid updated) To test I created the following data: <pre><code>CREATE TABLE location_byhits (Paid INT, IpAddress INT); CREATE TABLE Users (ID INT); CREATE TABLE Subscription(ItemID INT, UsersID INT); CREATE TABLE Item (ID INT, Item_TypeID INT); // INSERT location_byhits VALUES (0, '192.168.0.0'), (0, '192.168.0.1'); INSERT Users VALUES (1), (2); INSERT Subscription (1, 1), (2, 2); INSERT Item (1, 1), (2, 6); </code></pre> Then called the procedure 4 times with various parameters. <pre><code>CALL PaidUser ('192.168.0.2', 1); CALL PaidUser ('192.168.0.0', 3); CALL PaidUser ('192.168.0.1', 2); CALL PaidUser ('192.168.0.1', 1); </code></pre> <ul> <li>The first should not update as there is no IP Match.</li> <li>The Second should not update as there is no user match.</li> <li>The third should not update because although there is a user and an IP match, user 2 has no items associated that don't have an Item_TypeID of 6.</li> <li>The fourth should update because there is both a user match and an ip match, and the user has associated items that have and item type id other than 6.</li> </ul> I would be inclined to rewrite your stored procedure as follows though: <pre><code>CREATE PROCEDURE `PaidUser`(in IpId VARCHAR(45),in UId INT(20)) BEGIN UPDATE location_byhits SET Paid = 1 WHERE location_byhits.IpAddress = IpId AND EXISTS ( SELECT 1 FROM Users u INNER JOIN Subscription s ON u.ID = s.usersID INNER JOIN Item i ON i.ID = s.ItemID WHERE i.Item_TypeID != 6 AND u.ID = UId ); END </code></pre> If you do the check before the update, although it is very unlikely, it is not impossible that in between starting the check and doing the update the data is changed by another session. This could result in unexpected behaviour. If you do it all in a single statement you avoid concurrency issues. <a href="http://sqlfiddle.com/#!2/cc1c8/7" rel="nofollow">Example on SQL Fiddle</a>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMySql Stored Procedure not working
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USGarethD
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POMySql Stored Procedure not working
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. COi havent written here its default man :) and the problem is solved
 singulars
 PostPostId
 PO
 UserUserId
 USRiteish Jaiswal

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.