update and control flow
I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would be very useful for update operations, but I can't get it right. If I read the documentation correctly, it should be possible to say something like UPDATE X if WORD like 'a%' SET COMMENT = 'a' elseif WORD like 'b%' SET COMMENT = 'b' END IF But this gives me an error message. What am I doing wrong? MM Martin Mueller Professor emeritus of English and Classics Northwestern University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update and control flow
You can do that, but, perhaps the only chance to have it updating a row based on a condition is developing a Stored Procedure or even having a BEFORE Trigger associated with the main table. Those ways, you can test the sent value and decide on what UPDATE you will execute afterwards. Consider that this is just an opinion since I'm not part of the problem and cannot analyse all the requirements. WB 2014-12-09 12:25 GMT-02:00 Martin Mueller martinmuel...@northwestern.edu: I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would be very useful for update operations, but I can't get it right. If I read the documentation correctly, it should be possible to say something like UPDATE X if WORD like 'a%' SET COMMENT = 'a' elseif WORD like 'b%' SET COMMENT = 'b' END IF But this gives me an error message. What am I doing wrong? MM Martin Mueller Professor emeritus of English and Classics Northwestern University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update and control flow
You can use your login inline with nested IF expressions: insert into foo(id,comment) values(17, IF(WORD like 'a%','a',IF(word like 'b%','b',null))); On Tue, Dec 9, 2014 at 9:50 AM, wagnerbianchi.com m...@wagnerbianchi.com wrote: You can do that, but, perhaps the only chance to have it updating a row based on a condition is developing a Stored Procedure or even having a BEFORE Trigger associated with the main table. Those ways, you can test the sent value and decide on what UPDATE you will execute afterwards. Consider that this is just an opinion since I'm not part of the problem and cannot analyse all the requirements. WB 2014-12-09 12:25 GMT-02:00 Martin Mueller martinmuel...@northwestern.edu : I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would be very useful for update operations, but I can't get it right. If I read the documentation correctly, it should be possible to say something like UPDATE X if WORD like 'a%' SET COMMENT = 'a' elseif WORD like 'b%' SET COMMENT = 'b' END IF But this gives me an error message. What am I doing wrong? MM Martin Mueller Professor emeritus of English and Classics Northwestern University -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: Specking a small MySQL server
- Original Message - From: Richard Reina gatorre...@gmail.com Subject: Specking a small MySQL server somewhat of an energy hog and is due to be replaced. I was considering replacing it with a lap-top so as to conserve energy and because a laptop has a built in battery backup. Currently I have a couple of laptops running For a long time, my go-to recommendation for reliable laptops would've been thinkpads; but I haven't had any experience with the new chinese versions. I've been pretty happy with my (employer-issued) HP ProBooks, but that' a sample of one, of course :-) One thing to keep in mind for your particular usage, though, is that consumer drives, and, especially, laptop drives, are not designed for 24/7 operation. I would strongly recommend to go for SSD storage even if you don't need the speed, as those at least don't have moving parts. /johan -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
- Original Message - From: Sándor Halász h...@tbbs.net Subject: Re: forum vs email That is, this list, right? What does it lack (besides readers)? This list interacts with the forums on mysql.com? Every thread here matches one on there, and vice versa? (Honest question; I hardly ever visit the fora - but Shawn's earlier mention that he only time for one, not both, makes me think not so.) -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update and control flow
Hello Martin, On 12/9/2014 9:25 AM, Martin Mueller wrote: I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would be very useful for update operations, but I can't get it right. If I read the documentation correctly, it should be possible to say something like UPDATE X if WORD like 'a%' SET COMMENT = 'a' elseif WORD like 'b%' SET COMMENT = 'b' END IF But this gives me an error message. What am I doing wrong? The correct syntax is to put the function after the = sign. The column name must appear by itself on the left side of the equation. You must also use the function-format of IF or a CASE..END construction. UPDATE X SET COMMENT = IF(WORD like 'a%','a',if(WORD like 'b%', 'b',COMMENT)) UPDATE X SET COMMENT = CASE WHEN WORD like 'a%' then 'a' WHEN WORD like 'b%' then 'b' ELSE COMMENT END But, as you noted, it may be more efficient to simply run two UPDATE statements each with the appropriate WHERE clause to limit the changes to just those rows that match your conditions. UPDATE X SET COMMENT = 'a' WHERE WORD like 'a%' And you can combine both techniques to limit the scope of the UPDATE to just the rows to change by matching either pattern. UPDATE X SET COMMENT = IF(WORD like 'a%','a','b') WHERE WORD like 'a%' or WORD like 'b%' Note: this last format doesn't need the second if() in the 'else' portion of the first IF() function because the set of rows to be operated on is already limited by the WHERE clause. The rows will match one condition or the other but not neither. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
On 2014-12-09 9:55 AM, Johan De Meersman wrote: - Original Message - From: Sándor Halász h...@tbbs.net Subject: Re: forum vs email That is, this list, right? What does it lack (besides readers)? This list interacts with the forums on mysql.com? Nope. PB - Every thread here matches one on there, and vice versa? (Honest question; I hardly ever visit the fora - but Shawn's earlier mention that he only time for one, not both, makes me think not so.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
2014/12/09 15:20 -0600, Peter Brawley Nope. And why not? Because no one bothered to implement it? Now I (for the first time?) looked at forums.mysql.com and see more topics than on lists.mysql.com. The former is just more with-it, I guess. I believ that one could both by e-mail and through a webbrowser comment on a Google group. And one who reads sends e-mail through a webbrowser surely considers discussion through e-mail simplie more overhead than using his webbrowser for discussion, too. I further suspect e-mail clients on own computers are not in fashion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update and control flow
2014/12/09 14:25 +, Martin Mueller I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would be very useful for update operations, but I can't get it right. If I read the documentation correctly, it should be possible to say something like UPDATE X if WORD like 'a%' SET COMMENT = 'a' elseif WORD like 'b%' SET COMMENT = 'b' END IF But this gives me an error message. What am I doing wrong? You have seen Sean Green s good changes. You need to know where what form is allowed. There is an IF statement which is allowed within stored procedures and triggers, but not in queries or open code. There is also a function of the same name that takes three arguments: condition, TRUE choice, not-TRUE choice. There are also CASE statement and CASE operator, whereto WHEN belongs. The statement and operator look not quite alike: the statement ends with END CASE, the operator with END, and the ELSE NULL allowed for the operator is not allowed for the statement. CASE statement and operator, and IF statement, take THEN between the condition and the conclusion. (If you want to try the statement forms, write a trigger or a stored procedure.) The statement forms do not apply to your example. The function and operator forms that Sean Green used do, and also the conditions WHERE and HAVING, each with its own use. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
On 12/9/2014 9:10 PM, h...@tbbs.net wrote: 2014/12/09 15:20 -0600, Peter Brawley Nope. And why not? Because no one bothered to implement it? Now I (for the first time?) looked at forums.mysql.com and see more topics than on lists.mysql.com. The former is just more with-it, I guess. I believ that one could both by e-mail and through a webbrowser comment on a Google group. And one who reads sends e-mail through a webbrowser surely considers discussion through e-mail simplie more overhead than using his webbrowser for discussion, too. I further suspect e-mail clients on own computers are not in fashion. Well, the Forum does provide a bit less permanence than subscribing to a list. You can login, post your questions, then disappear without worrying about future emails about topics you may never be interested in. I guess we (the list members) are more dedicated than the forum users because we all recognize the usefulness of seeing a broad range of topics presented in an easily filterable and save-able format (email) over the web-based content of the forums. I also find it easier to monitor the emails than the forum simply because the most recent response to a list topic does not automatically reposition the topic to the top of the list. It's harder to lose a question in the noise when I can see what I have tagged as read/unread. I can't do that in the forums. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
