SQL update to make a toggle

Question

I want to make a toggle request as defined there T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition

I did this :

Update capteur 
join smartparking_reference on(smartparking_reference.id_capteur = capteur.id_capteur) 
set (CASE WHEN capteur.valeur != 0 then capteur.valeur = 0 and last_value_date = now() END) 
where smartparking_reference.id_ref = 3;

But always a syntax error. So what did i miss ?

This `set (CASE WHEN capteur.valeur != 0 then capteur.valeur = 0 and last_value_date = now() END) ` seems very suspicious indeed! Are you trying to update two fields? — Giorgos Altanis, Apr 20 '17 at 15:05
You say you get a syntax error. May we know the error message? — René Hoffmann, Apr 20 '17 at 15:06
@GiorgosAltanis That's right, i want to update the date when something new arrive rene i'm not able to do this for now, i'm not int the office anymore. Wiil keep you abreast tomorrow — Little squirrel, Apr 20 '17 at 15:08
Your `case` statement is really a mess, as well as your update. Obviously you should study both `update` and `case` constructs, but your task can be performed without a case. — Giorgos Altanis, Apr 20 '17 at 15:12

Giorgos Altanis · Answer 1 · 2017-04-20T20:15:22.287

1

Apparently you want to update capteur.valeur only when it is nonzero and set it to zero. Try this simpler statement:

MySql style

Update capteur 
join smartparking_reference on(smartparking_reference.id_capteur = capteur.id_capteur) 
set valeur = case when valeur = 0 then 1 else 0 end, last_value_date = now()
where smartparking_reference.id_ref = 3;

Sql Server style

Update capteur 
set valeur = case when valeur = 0 then 1 else 0 end, last_value_date = getdate()
from smartparking_reference
where smartparking_reference.id_capteur = capteur.id_capteur 
and smartparking_reference.id_ref = 3;

edited Apr 20 '17 at 20:15

answered Apr 20 '17 at 15:08

Giorgos Altanis

2,742
1
13
14

Well it's not exactly the good way. I don't have any information about the new state of the item, and this query need to operate like a switch, so if the value is 0 i want to store 1 and the other way (from 1 to 0). So i've tried this to perform only 1 query – Little squirrel Apr 20 '17 at 17:15
Well sorry again but i confirm my last comment, this will not solve my problem, please check it out – Little squirrel Apr 20 '17 at 17:44
I am not sure I understand your problem exactly, I showed you what to do to make your query work without syntax error. Now if you want to actually toggle check my updated answer, which implements this. Note however that your original query was not even implying this. – Giorgos Altanis Apr 20 '17 at 20:17

score 0 · Answer 2 · answered Apr 20 '17 at 15:12

0

I also guess your update statement has the wrong syntax

UPDATE c
SET valeur = 0, last_value_date = now()
FROM capteur c
  INNER JOIN smartparking_reference AS sr ON 
    sr.id_capteur = c.id_capteur
WHERE sr.id_ref = 3 and c.valeur != 0

answered Apr 20 '17 at 15:12

Kevin

751
6
12

score -1 · Accepted Answer · answered Apr 20 '17 at 15:19

UPDATE capteur
SET capteur.valeur=(
    CASE
    WHEN capteur.valeur != 0 THEN
        0 ELSE 'another value'
    END
),last_value_date = now ()
FROM capteur
INNER JOIN smartparking_reference ON 
    smartparking_reference.id_capteur = capteur.id_capteur
WHERE
    smartparking_reference.id_ref = 3;

SQL update to make a toggle

3 Answers3