Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a sqlite table and try to update the status to yes or no. If status says yes means have the link and says no it means no have the link.

How I can write update sqlite like this situation. Because I searching google more on update all but not specific the situation.

Here is output that I want:
| Matric | Name                         | GitHub Link               | Status |
|--------|------------------------------|---------------------------|--------|
| 2XXXXX | YXXXXXXXXXX                  | https://github.com/abcde  | Yes    |
| 2XXXXX |NAME STUDENTXXXXXXXXXXXXXXXX  |                           | No     |


What I have tried:

I have tried to update to be specify like situation yes or no. but I run this they says
[SQLITE_ERROR] SQL error or missing database (near "WHERE": syntax error)
[SQLITE_ERROR] SQL error or missing database (near "WHERE": syntax error)


package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Update {

    private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:C://sqlite/db/test2.db";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }
    public void update(String status) {
        String sql = "UPDATE nameListss SET status = ? , WHERE github = null else ? ,";

        try (Connection conn = this.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setString(1, status);
            pstmt.setString(1, status);

            // update
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

        Update app = new Update();
        // update the warehouse with id 3
        app.update("No");
        app.update("Yes");
    }

}
Posted
Updated 19-Apr-21 22:41pm

Your SQL statement is incorrect. You have commas where there should be none and you need a CASE statement. E.g.
SQL
UPDATE nameListss SET status = CASE WHEN github is null then ? ELSE ? END;
 
Share this answer
 
v3
Comments
Cow cow Lee 20-Apr-21 4:50am    
ok thanks advice, when run this code thrown another error" Values not bound to statement".In this situation is my github colum not have null so run this error?
CHill60 20-Apr-21 8:19am    
Values not bound to statement usually means that the parameters have not been set up correctly. You currently have
            // set the corresponding param
            pstmt.setString(1, status);
            pstmt.setString(1, status);
I.e. you are not setting the second parameter. So EITHER change your code to set up both parameters
            // set the corresponding param
            pstmt.setString(1, status);
            pstmt.setString(2, status);
OR hard-code the parameters directly into the statement. As you have accepted Solution 2 as the best answer I assume that is the direction you have taken so just accept this comment as a learning point
Cow cow Lee 20-Apr-21 8:38am    
Thanks is work for me !!!
Try like this:
SQL
UPDATE MyTable SET MyColumn = CASE WHEN ISNULL(value) 
                                   THEN 'No'
                                   ELSE 'Yes'
                              END
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900