Update query wrong in MySQL

Question

I'm hope I doing everything in the right path. I wrote a shell script, if the condition matched, it will run update query.

#!/bin/bash
mysql -u root -pPassword <<rc
use rc;
SELECT *,
       CASE 
         WHEN cutoff_dt IS NULL
       THEN 
         UPDATE rc SET cutoff_dt = '2017-03-21 00:00:00.0'
         ELSE 'NOT NULL'
      END
from rc
WHERE business_date = '2017-03-21 16:50:29.032';
rc

Unfortunately, I get error

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE rc SET cutoff_dt = '2017-03-21 00:00:00.0'
         ELSE ' at line 5

Latest code

The answer given doesn't helped ! Maybe I'm doing wrong ?

#!/bin/bash
mysql -u root -pPassword <<rc
use rc;
SELECT *,
       CASE 
         WHEN cutoff_dt IS NULL
       THEN 
        UPDATE mepslog
        SET cutoff_dt = '2017-03-21 23:57:19'
  WHERE business_date = '2017-03-21 00:00:00.000'
    AND cutoff_dt IS NULL
      END
from rc
WHERE business_date = '2017-03-21 00:00:00.000';
rc

you cannot mix select statement with update. You need to write a stored procedure or just mask the output in the select statement. — Shadow, Mar 21 '17 at 15:41
I don't know because your question lacks the description of what your are trying to achieve. — Shadow, Mar 21 '17 at 15:44
@Shadow `Select * from rc where business_date = '2017-03-21 16:50:29.032'. IF `cutoff_dt` is null or empty, it will update, otherwise display not null.` — AI., Mar 21 '17 at 15:46
Do you actually want to change the data or you just want to display it? — Shadow, Mar 21 '17 at 15:47
If you want to update it, then you need to use a separate update statement. However, in a select statement you can represent the underlying data differently without actually updating the data - that's what I meant by masking. In the select statement in place of the update statement you can simply write `THEN '2017-03-21 23:57:19'` and that's what you are going to see in place of the null values. — Shadow, Mar 21 '17 at 17:21

score 4 · Accepted Answer · edited Mar 21 '17 at 15:46

4

You cant mix such queries. Instead you would extend the query's where-clause.

A working update would look like as following:

UPDATE rc
  SET cutoff_dt = '2017-03-21 00:00:00.0'
  WHERE business_date = '2017-03-21 16:50:29.032'
    AND cutoff_dt IS NULL

edited Mar 21 '17 at 15:46

René Hoffmann

2,766
2
20
43

answered Mar 21 '17 at 15:43

T0rb1nh0

181
6

Just replace `UPDATE rc SET cutoff_dt = '2017-03-21 00:00:00.0'` to your code ? – AI. Mar 21 '17 at 15:44
well, I still get the same error after replace yours with mine – AI. Mar 21 '17 at 15:52
Please show us what you have tried as well as the error message itself. Thanks! – T0rb1nh0 Mar 21 '17 at 15:59
Just get rid of that nasty `select` statement. You do not need it. You only have to run the `update` statement above. – René Hoffmann Mar 21 '17 at 16:01
@RenéHoffmann But I want it select data which the `business_date = '2017-03-21 00:00:00.000'` – AI. Mar 21 '17 at 16:08
In your question's comments you wrote *I want update it*. Now, you write *I want it select data*. You have to decide! If you want to do both, you have to **do it subsequently**. First update those records who have `cutoff_dt is null`. Then, you can read all the data. – René Hoffmann Mar 21 '17 at 16:14
If you select first, some records will still have `cutoff_dt is null`. If that is, what you want, fine. I understood your question, as that you want to update those records. The answer does exactly this. If you still want to output the data, then you can select it afterwards and recieve the data without any `cutoff_dt is null`. – René Hoffmann Mar 21 '17 at 16:24
@RenéHoffmann So the latest code (from my post) is the correct one ? – AI. Mar 21 '17 at 16:25
No, the "latest code" from your answer is not runnable. You noticed that by yourself. I extended Lonewolff's answer (again) to clarify. I wont explain further, as I think this is completely understandable. If you don't understand it, you have to read further into SQL or ask further **specific questions**. – René Hoffmann Mar 21 '17 at 16:31
@AI. I feel you're confusing something about how `UPDATE` works. @RenéHoffmann and @Lonewolf are trying to ask you a question that you're probably misinterpreting. Think about the final result you want to achieve (1) Do you want the tabular output to be produced AFTER you've executed the query? (2) Or, do you only want to `UPDATE` the table for records which you'd be able to choose if you ran `SELECT * from rc WHERE business_date = '2017-03-21 16:50:29.032' AND cutoff_dt IS NULL` (but without actually bothering to "see" them and only being concerned about the update made to the records)? – Dhruv Saxena Mar 21 '17 at 16:34
If you say (1): then you need `SELECT` for that, but would have to be run BEFORE / AFTER the `UPDATE`, depending on when and how you want to "see" the data. On the other hand, if you say (2): then please know that SQL will implicitly "select" records for update when you specify the `WHERE` condition. From the syntax you've used in the question, it appears that you've probably written it like a structural programming language (C, PHP, Java, etc) and SQL doesn't work that way. – Dhruv Saxena Mar 21 '17 at 16:35
@DhruvSaxena I want to achieve (2). It should update the records without tabular output. – AI. Mar 21 '17 at 16:41
Then please simply use the query as posted in the answer (delete everything except bash commands from your question). What you call "select.. then... update" is implicit when a `WHERE` condition is specified in the `UPDATE` query. It's not required to be hugely complicated :) – Dhruv Saxena Mar 21 '17 at 16:50
@DhruvSaxena But I need to write in [shell script](https://www.cyberciti.biz/faq/using-mysql-in-shell-scripts/) and also with the [if else](http://stackoverflow.com/a/8763381/2987882) condition – AI. Mar 21 '17 at 16:58
@RenéHoffmann I need to write in [shell script](https://www.cyberciti.biz/faq/using-mysql-in-shell-scripts/), using [if else](http://stackoverflow.com/a/8763381/2987882) condition – AI. Mar 21 '17 at 17:03
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/138665/discussion-between-dhruv-saxena-and-ai). – Dhruv Saxena Mar 21 '17 at 17:58

Update query wrong in MySQL

1 Answers1

Linked