CODEWITHSUNDEEP
sqlsql-server
Reece Hewitson
Reece Hewitson

Reputation: 75

My If statement wont work

Im trying to use a sql if statement based on a current record in my database, the code goes:

if((Select distinct STUDENT_ID, FLAG, YEAR From EF_STUDENTFLAGS Where STUDENT_ID = '100143795' and YEAR = '16/17').ROW_NUMBER <1)

INSERT INTO EF_STUDENTFLAGS (STUDENT_ID, FLAG, YEAR, COMMENTS, LOGGED_BY, SUBDATE, START_DATE, END_DATE) VALUES ('100143795','Safeguarding','16/17','Test','rhewitson','2014-09-03','2017-02-01','2017-02-01')

else

Update EF_STUDENTFLAGS Set COMMENTS = 'Test', LOGGED_BY = 'rhewitson', SUBDATE = '2014-09-03', START_DATE = '2017-02-01', END_DATE = '2017-02-01' Where STUDENT_ID = '100143795' and YEAR = '16/17'

The error message i get is:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

what am i doing wrong??

EDIT:

If there isnt already a record in my database with the same Student id, flag and year as the one i am entering, i want it to create one and if it does exist i want it to update the current record to reflect the most recent version

Upvotes: 0

Views: 67

Answers (1)

Stephen
Stephen

Reputation: 1542

try this:

if Not Exists(Select top 1 1 From EF_STUDENTFLAGS Where STUDENT_ID = '100143795' and YEAR = '16/17')
    INSERT INTO EF_STUDENTFLAGS (STUDENT_ID, FLAG, YEAR, COMMENTS, LOGGED_BY, SUBDATE, START_DATE, END_DATE) VALUES ('100143795','Safeguarding','16/17','Test','rhewitson','2014-09-03','2017-02-01','2017-02-01')
else
    UPDATE EF_STUDENTFLAGS Set COMMENTS = 'Test', LOGGED_BY = 'rhewitson', SUBDATE = '2014-09-03', START_DATE = '2017-02-01', END_DATE = '2017-02-01' Where STUDENT_ID = '100143795' and YEAR = '16/17'

Upvotes: 1

Related Questions