The SQL UPDATE statement syntax follows the format:
UPDATE [column name1] = [new value1], [column name2] = [new value2], ... FROM [table] WHERE [CONDITION]The statement can have a list of column value pairs to update the value from a table that satisfies the where condition. Of course the where condition is optional if we wish to update all fields in the table. Below is an example on how to use this to update data in a table using Groovy SQL.
package sample import groovy.sql.Sql import java.sql.* /** * A simple Groovy example that updates data from a database table. */ class Sample { static void main(String[] args) { // connect to db def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test", "root", "root", "com.mysql.jdbc.Driver") // create table sql.execute(""" CREATE TABLE PERSON ( FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50) )"""); // Create 1 data sql.execute("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values ('John', 'Doe')"); // Output the first name sql.eachRow("SELECT FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p -> println "First Name Before Update= ${p.FIRST_NAME}" } // Update the first name sql.execute("UPDATE PERSON SET FIRST_NAME = 'JANE' WHERE LAST_NAME = 'Doe'"); // Output the first name again sql.eachRow("SELECT FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p -> println "First Name After Update= ${p.FIRST_NAME}" } // close connection sql.close() } }Here we hardcoded the query to be "UPDATE PERSON SET FIRST_NAME = 'JANE' WHERE LAST_NAME = 'Doe'". The output will display the value of the first name before and after the update statement. Hence the console will display:
First Name Before Update= John First Name After Update= JANEWe can also use named parameter in our update statements, here is a modified example:
package sample import groovy.sql.Sql import java.sql.* /** * A simple Groovy example that updates data from a database table. */ class Sample { static void main(String[] args) { // connect to db def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test", "root", "root", "com.mysql.jdbc.Driver") // create table sql.execute(""" CREATE TABLE PERSON ( FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50) )"""); // Create 1 data sql.execute("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values ('John', 'Doe')"); // Output the first name sql.eachRow("SELECT FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p -> println "First Name Before Update= ${p.FIRST_NAME}" } // Update the first name sql.execute("UPDATE PERSON SET FIRST_NAME = :FNAME WHERE LAST_NAME = :LNAME", [FNAME:'Jane', LNAME:'Doe']); // Output the first name again sql.eachRow("SELECT FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p -> println "First Name After Update= ${p.FIRST_NAME}" } // close connection sql.close() } }We use the parameter FNAME and LNAME in our query, and set the value using a parameter map "[FNAME:'Jane', LNAME:'Doe']". And the code will have the same output as the first example.