update to 'unvaccinated' only if table1.Vaccinated = 'YES' for the row that said-CodePudding

DROP TABLE table1;

CREATE TABLE table1
(
    Vaccinationstatus VARCHAR(10),
    Vaccinated VARCHAR(3)
);

INSERT INTO table1 (Vaccinationstatus)
VALUES ('yes');

SELECT * FROM table1;

UPDATE table1
SET table1.Vaccination_status = 'Vaccinated'
WHERE table1.Vaccinated = 'yes'

UPDATE table1
SET table1.Vaccination_status = 'Vaccinated'
CASE
WHEN table1_Vaccinated = 'Yes'
ELSE Vaccination_status 
-- If the table1.Vaccinated  say 'yes' depending on the row which said yes update the table1.Vaccination_status to 'Vaccinated' for those row that said yes only--
END;

Error I keep getting:

ORA-00933: SQL command not properly ended.

I'm trying to update table1.Vaccination_status to update to unvaccinated only if table1.Vaccinated = 'YES' for the row that said yes AND don't update for people that said no.

I want to also make it so that it only update if those conditional are meet. I tried an update statement but I don't think I am doing it right.

Any ideas?

CodePudding user response：

It's case sensitive. So 'Yes' doesn't equal 'yes'

To be sure, you could use COLLATION, or use LOWER or UPPER.

Example

CREATE TABLE table1 (
 VictimId int, 
 VaccinationStatus VARCHAR2(10),
 Vaccinated VARCHAR2(3) DEFAULT 'no'
);

INSERT ALL
INTO table1 (VictimId, Vaccinated) VALUES (1, 'yes')
INTO table1 (VictimId) VALUES(2)
SELECT 1 FROM DUAL;

SELECT * From table1;

VICTIMID | VACCINATIONSTATUS | VACCINATED
-------: | :---------------- | :---------
       1 | null              | yes       
       2 | null              | no

UPDATE table1
SET VaccinationStatus = 'Vaccinated'
WHERE lower(Vaccinated) = lower('Yes')

SELECT * From table1;

VICTIMID | VACCINATIONSTATUS | VACCINATED
-------: | :---------------- | :---------
       1 | Vaccinated        | yes       
       2 | null              | no

db<>fiddle here

CodePudding user response：

Based on the error, you're missing the point of the feedback you're getting. There's a syntax error: you're missing a semi-colon at the end of one of your statements:

UPDATE table1 SET table1.Vaccination_status = 'Vaccinated' WHERE table1.Vaccinated = 'yes';

Aside from that, indeed make sure to spell 'Yes' and 'yes' in the same manner. I'd recommend using a numerical column with either values 0 (no, false) or 1 (yes, true). That'll prevent these types of common mistakes.