MySQL Forums
Forum List  »  Stored Procedures

Re: Having trouble with Procedure
Posted by: Peter Brawley
Date: October 10, 2017 04:18PM

> I come from Microsoft ...

Unlike many kinds of brain damage, the M$oft-induced variety is remediable, but it takes time :-).

To preserve formatting for human readability in these fora, use BBCode code tags.

> select @source_id = list_id

That returns a comparison of @source_id and list_id. Not what you intended. To assign list_id to the user var, use the assignment operator: select @source_id := list_id ...

But I don't see why this sproc is needed. You appear to be thinking procedurally about the problem, as if it must be tackled row-by-row. SQL is set-based. Why not write a straightforward query, something like ...

insert into test_list_subscriber_action
  (source_list_id, source_action, target_list_id, target_action)
  select ...
  from subscriber_action a 
  left join mw_list m on m.source_id = a.source_id 
                     and m.list_id > m.dest_id 
                     and m.list_id <> m.source_id
                     and m.target_list_id = m.dest_id 
                     and m.source_action = 'unsubscribe'
                     and m.target_action = 'unsubscribe'
  where m.source_id is null;

I'm sure that gets many details wrong, but the general method looks a fit for the job you're trying to do, with unavoidable awkwardness, row-by-row.

Options: ReplyQuote


Subject
Views
Written By
Posted
3262
October 10, 2017 02:31PM
523
October 10, 2017 03:41PM
Re: Having trouble with Procedure
528
October 10, 2017 04:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.