-1

Can someone tell me what is invalid about this statement? I haev tried a few different varations of this but none show as valid. I have checked SO and it says it should be valid but is not. Is it due to the BETWEEN condition?

UPDATE mytablename SET column_2 = IF({BETWEEN '1' AND '2'}, {new_value}, field_1) WHERE id=1

UPDATE mytablename SET column_2 = IF(column_1== 1{BETWEEN '1' AND '2'}, 1{12}, field_1) WHERE id=1

UPDATE 1 I have tried this below but same result of invalid -

UPDATE mytablename SET column_2 = IF(field_1 BETWEEN 1 AND 2, 12, field_1) WHERE id = 1

UPDATE 2 I also tried this but it too is invalid:

UPDATE mytablename SET column_2 = 12 IF(field_1 BETWEEN 1 AND 2) WHERE id = 1

UPDATE 3 Also not valid. Unexpected "{":

UPDATE mytablename SET column_2 = IF(field_1 BETWEEN '1' AND '2', {12}, column_2 ) WHERE id=1;
Mikey
  • 23
  • 6
  • yes Between needs before a column like in your second update, without it is not valid – nbk Oct 11 '20 at 19:46
  • @nbk thanks. But the second is not valid. According to below nothing is valid with {} – Mikey Oct 11 '20 at 20:00
  • your updat1 works just fine as ling as you have column_1 and field_1 in your database, l,lease don't mix up syntax error with other errors and check the error message – nbk Oct 11 '20 at 20:18
  • I do have a field 1 and a column 1 but it is literally telling me that the { is an unexpected character with the red X next to the whole query meaning it is not valid syntax – Mikey Oct 11 '20 at 20:29
  • see my answer i added a dbfiddle to show you that it works, change the fiddle to experiment – nbk Oct 11 '20 at 20:39

2 Answers2

0

(a) The curly brackets don't belong there.

(b) If the literals are meant to be integer literals, don't single quote them.

(c) In (standard) SQL the comparison operator is = not ==. MySQL might accept the latter but your code gets more portable if you use =.

(d) You need an operand to check. So the syntax is operand_to_check BETWEEN operand_lower_bound AND operand_upper_bound.

Just if(BETWEEN 1 AND 2, ...) is therefore wrong.

if(column_1 = 1 BETWEEN 1 AND 2, ... is technically OK but probably doesn't do what you expect. What it does is to first compare column_1 and 1. If they're equal the result of that operation is 1 otherwise it's 0. Then it checks if that 0 or 1 is between 1 and 2.

I guess what you want is if(column_1 BETWEEN 1 AND 2, ...)

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • thanks. I am updating above since it is still showing invalid. Maybe I am misunderstanding the , .... – Mikey Oct 11 '20 at 19:55
  • @Mikey; The `...` are just place holders for the rest of your code. You have to replace them with whatever 2nd and 3rd argument you have in mind for `if()`. – sticky bit Oct 11 '20 at 19:57
  • I updated above I ddint have any other arguments in mind there, just needing to set the result if the condition is met but youre saying that above is not valid to do so. Thus, what would the valid code be to set the condition IF x is met? – Mikey Oct 11 '20 at 20:00
  • @Mikey: `if()` takes three arguments. So you need to have two more arguments... – sticky bit Oct 11 '20 at 20:01
  • what do I do if I dont have three arguments? I have seen a number of examples with only one argument. What am I missing? All I am trying to do is update a value if column_1 is has a value between 1 and 2 – Mikey Oct 11 '20 at 20:06
  • Why would you use `if()` if you haven't three arguments for it? This seems to lead to nowhere. You better delete this question and ask a new one where you elaborate on what you're trying to do. Include a [example] in the new question, i.e. the `CREATE` statement of the table and `INSERT` statements with sample data. Also include the expected result with that sample data and of course again what you have tried. Describe exactly how what you've tried failed. If there's any error messages, include them. – sticky bit Oct 11 '20 at 20:16
0

As i said in the comment you need a column

UPDATE mytablename SET column_2 = IF(column_1 BETWEEN '1' AND '2', {new_value}, field_1) WHERE id=1;

{new_vaue} is only a place holder which you must fill with a value or cloumn see below

UPDATE mytablename SET column_2 = IF(column_1 BETWEEN '1' AND '2', 5, field_1) WHERE id=1;

UPDATE mytablename SET column_2 = IF(column_1 BETWEEN '1' AND '2', coumn3, field_1) WHERE id=1;

Example

CREATE TABLE mytablename(id int,column_2 int, field_1 int);

INSERT INTO mytablename VALUES (1,0,1)

UPDATE mytablename SET column_2 = IF(field_1 BETWEEN 1 AND 2, 12, field_1) WHERE id = 1

SELECT * FROM mytablename

id | column_2 | field_1
-: | -------: | ------:
 1 |       12 |       1

db<>fiddle here

nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thanks. This is showing as invalid according to MYPHPADMIN. I can not even run the query. Its telling me the {} are invalid – Mikey Oct 11 '20 at 20:09
  • {newvalue} is of ycourse in valid, there must be a column or a value – nbk Oct 11 '20 at 20:11
  • Yes thanks. I added the values of course and it is telling me anything with {} are invalid. I filled everything out of course – Mikey Oct 11 '20 at 20:13
  • your queries arent unvalid, but your database must have a columname field_1 and a column_1 – nbk Oct 11 '20 at 20:16
  • Thank you. Removing the {} was the answer. I appreciate the fiddle and the work – Mikey Oct 11 '20 at 20:57