UpdateTest.java
/*-
* #%L
* JSQLParser library
* %%
* Copyright (C) 2004 - 2019 JSQLParser
* %%
* Dual licensed under GNU LGPL 2.1 or Apache License 2.0
* #L%
*/
package net.sf.jsqlparser.statement.update;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BooleanValue;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.test.TestUtils;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.ValueSource;
import java.io.StringReader;
import java.util.List;
import static net.sf.jsqlparser.test.TestUtils.assertOracleHintExists;
import static net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed;
import static net.sf.jsqlparser.test.TestUtils.assertUpdateMysqlHintExists;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertInstanceOf;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
public class UpdateTest {
private static final CCJSqlParserManager PARSER_MANAGER = new CCJSqlParserManager();
@Test
public void testUpdate() throws JSQLParserException {
String statement = "UPDATE mytable set col1='as', col2=?, col3=565 Where o >= 3";
Update update = (Update) PARSER_MANAGER.parse(new StringReader(statement));
assertEquals("mytable", update.getTable().toString());
assertEquals(3, update.getUpdateSets().size());
assertEquals("col1", update.getUpdateSets().get(0).getColumns().get(0).getColumnName());
assertEquals("col2", update.getUpdateSets().get(1).getColumns().get(0).getColumnName());
assertEquals("col3", update.getUpdateSets().get(2).getColumns().get(0).getColumnName());
assertEquals("as",
((StringValue) update.getUpdateSets().get(0).getValues().get(0)).getValue());
assertTrue(update.getUpdateSets().get(1).getValues().get(0) instanceof JdbcParameter);
assertEquals(565,
((LongValue) update.getUpdateSets().get(2).getValues().get(0)).getValue());
assertTrue(update.getWhere() instanceof GreaterThanEquals);
}
@Test
public void testUpdateWAlias() throws JSQLParserException {
String statement = "UPDATE table1 A SET A.columna = 'XXX' WHERE A.cod_table = 'YYY'";
Update update = (Update) PARSER_MANAGER.parse(new StringReader(statement));
}
@Test
public void testUpdateWithDeparser() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE table1 AS A SET A.columna = 'XXX' WHERE A.cod_table = 'YYY'");
}
@Test
public void testUpdateWithFrom() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE table1 SET columna = 5 FROM table1 LEFT JOIN table2 ON col1 = col2");
}
@Test
public void testUpdateMultiTable() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE T1, T2 SET T1.C2 = T2.C2, T2.C3 = 'UPDATED' WHERE T1.C1 = T2.C1 AND T1.C2 < 10");
}
@Test
public void testUpdateWithSelect() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE NATION SET (N_NATIONKEY) = (SELECT ? FROM SYSIBM.SYSDUMMY1)");
}
@Test
public void testUpdateWithSelect2() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE mytable SET (col1, col2, col3) = (SELECT a, b, c FROM mytable2)");
}
@Test
public void testUpdateIssue167_SingleQuotes() throws JSQLParserException {
String sqlStr =
"UPDATE tablename SET NAME = 'Customer 2', ADDRESS = 'Address \\' ddad2', AUTH_KEY = 'samplekey' WHERE ID = 2";
assertSqlCanBeParsedAndDeparsed(
sqlStr, true, parser -> parser.withBackslashEscapeCharacter(true));
}
@Test
public void testUpdateWithLimit() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed("UPDATE tablename SET col = 'thing' WHERE id = 1 LIMIT 10");
}
@Test
public void testUpdateWithOrderBy() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 ORDER BY col");
}
@Test
public void testUpdateWithOrderByAndLimit() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 ORDER BY col LIMIT 10");
}
@Test
public void testUpdateWithReturningAll() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 ORDER BY col LIMIT 10 RETURNING *");
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 RETURNING *");
}
@Test
public void testUpdateWithReturningList() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 ORDER BY col LIMIT 10 RETURNING col_1, col_2, col_3");
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 RETURNING col_1, col_2, col_3");
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 ORDER BY col LIMIT 10 RETURNING col_1 AS Bar, col_2 AS Baz, col_3 AS Foo");
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 RETURNING col_1 AS Bar, col_2 AS Baz, col_3 AS Foo");
assertSqlCanBeParsedAndDeparsed(
"UPDATE tablename SET col = 'thing' WHERE id = 1 RETURNING ABS(col_1) AS Bar, ABS(col_2), col_3 AS Foo");
}
@Test
public void testUpdateDoesNotAllowLimitOffset() {
String statement =
"UPDATE table1 A SET A.columna = 'XXX' WHERE A.cod_table = 'YYY' LIMIT 3,4";
assertThrows(JSQLParserException.class,
() -> PARSER_MANAGER.parse(new StringReader(statement)));
}
@Test
public void testUpdateWithFunctions() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed("UPDATE tablename SET col = SUBSTRING(col2, 1, 2)");
}
@Test
public void testUpdateIssue508LeftShift() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed("UPDATE user SET num = 1 << 1 WHERE id = 1");
}
@Test
public void testUpdateIssue338() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed("UPDATE mytable SET status = (status & ~1)");
}
@Test
public void testUpdateIssue338_1() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed("UPDATE mytable SET status = (status & 1)");
}
@Test
public void testUpdateIssue338_2() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed("UPDATE mytable SET status = (status + 1)");
}
@Test
public void testUpdateIssue826() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed("update message_topic inner join message_topic_config on\n"
+ " message_topic.id=message_topic_config.topic_id \n"
+ "set message_topic_config.enable_flag='N', \n"
+ "message_topic_config.updated_by='test', \n"
+ "message_topic_config.update_at='2019-07-16' \n"
+ "where message_topic.name='test' \n"
+ "AND message_topic_config.enable_flag='Y'", true);
}
@Test
public void testUpdateIssue750() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"update a,(select * from c) b set a.id=b.id where a.id=b.id", true);
}
@Test
public void testUpdateIssue962Validate() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE tbl_user_card SET validate = '1', identityCodeFlag = 1 WHERE id = 9150000293816");
}
@Test
public void testUpdateVariableAssignment() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE transaction_id SET latest_id_wallet = (@cur_id_wallet := latest_id_wallet) + 1");
}
@Test
public void testOracleHint() throws JSQLParserException {
assertOracleHintExists(
"UPDATE /*+ SOMEHINT */ mytable set col1='as', col2=?, col3=565 Where o >= 3", true,
"SOMEHINT");
// @todo: add a testcase supposed to not finding a misplaced hint
// assertOracleHintExists("UPDATE mytable /*+ SOMEHINT */ set col1='as', col2=?, col3=565
// Where o >= 3", true, "SOMEHINT");
}
@Test
public void testMysqlHint() throws JSQLParserException {
assertUpdateMysqlHintExists(
"UPDATE demo FORCE INDEX (idx_demo) SET col1 = NULL WHERE col2 = 1", true, "FORCE",
"INDEX", "idx_demo");
}
@Test
public void testWith() throws JSQLParserException {
String statement = ""
+ "WITH a\n"
+ " AS (SELECT 1 id_instrument_ref)\n"
+ " , b\n"
+ " AS (SELECT 1 id_instrument_ref)\n"
+ "UPDATE cfe.instrument_ref\n"
+ "SET id_instrument=null\n"
+ "WHERE id_instrument_ref = (SELECT id_instrument_ref\n"
+ " FROM a)";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(statement, true);
List<WithItem<?>> withItems = update.getWithItemsList();
assertEquals("cfe.instrument_ref", update.getTable().getFullyQualifiedName());
assertEquals(2, withItems.size());
assertEquals("SELECT 1 id_instrument_ref",
withItems.get(0).getSelect().getPlainSelect().toString());
assertEquals(" a", withItems.get(0).getAlias().toString());
assertEquals("SELECT 1 id_instrument_ref",
withItems.get(1).getSelect().getPlainSelect().toString());
assertEquals(" b", withItems.get(1).getAlias().toString());
assertEquals(1, update.getUpdateSets().size());
assertEquals("id_instrument", update.getUpdateSets().get(0).getColumn(0).toString());
assertEquals("NULL", update.getUpdateSets().get(0).getValue(0).toString());
assertEquals("id_instrument_ref = (SELECT id_instrument_ref FROM a)",
update.getWhere().toString());
}
@Test
public void testUpdateSetsIssue1316() throws JSQLParserException {
String sqlStr = "update test\n"
+ "set (a, b) = (select '1', '2')";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "update test\n"
+ "set a = '1'"
+ " , b = '2'";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "update test\n"
+ "set (a, b) = ('1', '2')";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "update test\n"
+ "set (a, b) = (values ('1', '2'))";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "update test\n"
+ "set (a, b) = (1, (select 2))";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "UPDATE prpjpaymentbill b\n"
+ "SET ( b.packagecode\n"
+ " , b.packageremark\n"
+ " , b.agentcode ) = ( SELECT p.payrefreason\n"
+ " , p.classcode\n"
+ " , p.riskcode\n"
+ " FROM prpjcommbill p\n"
+ " WHERE p.policertiid = 'SDDH200937010330006366' ) -- this is supposed to be UpdateSet 1\n"
+ " , b.payrefnotype = '05' -- this is supposed to be UpdateSet 2\n"
+ " , b.packageunit = '4101170402' -- this is supposed to be UpdateSet 3\n"
+ "WHERE b.payrefno = 'B370202091026000005'";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(sqlStr, true);
assertEquals(3, update.getUpdateSets().size());
assertEquals(3, update.getUpdateSets().get(0).getColumns().size());
assertEquals(1, update.getUpdateSets().get(0).getValues().size());
assertEquals(1, update.getUpdateSets().get(1).getColumns().size());
assertEquals(1, update.getUpdateSets().get(1).getValues().size());
assertEquals(1, update.getUpdateSets().get(2).getColumns().size());
assertEquals(1, update.getUpdateSets().get(2).getValues().size());
}
@Test
public void testUpdateLowPriority() throws JSQLParserException {
String stmt = "UPDATE LOW_PRIORITY table1 A SET A.columna = 'XXX'";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(stmt);
assertEquals(update.getModifierPriority(), UpdateModifierPriority.LOW_PRIORITY);
}
@Test
public void testUpdateIgnoreModifier() throws JSQLParserException {
String stmt = "UPDATE IGNORE table1 A SET A.columna = 'XXX'";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(stmt);
assertTrue(update.isModifierIgnore());
String stmt2 = "UPDATE table1 A SET A.columna = 'XXX'";
Update update2 = (Update) assertSqlCanBeParsedAndDeparsed(stmt2);
assertFalse(update2.isModifierIgnore());
}
@Test
public void testUpdateMultipleModifiers() throws JSQLParserException {
String stmt = "UPDATE LOW_PRIORITY IGNORE table1 A SET A.columna = 'XXX'";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(stmt);
assertEquals(update.getModifierPriority(), UpdateModifierPriority.LOW_PRIORITY);
assertTrue(update.isModifierIgnore());
}
@Test
public void testUpdateOutputClause() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(
"UPDATE /* TOP (10) */ HumanResources.Employee \n"
+ "SET VacationHours = VacationHours * 1.25, \n"
+ " ModifiedDate = GETDATE() \n"
+ "OUTPUT inserted.BusinessEntityID, \n"
+ " deleted.VacationHours, \n"
+ " inserted.VacationHours, \n"
+ " inserted.ModifiedDate \n"
+ "INTO @MyTableVar",
true);
assertSqlCanBeParsedAndDeparsed(
"UPDATE Production.WorkOrder \n"
+ "SET ScrapReasonID = 4 \n"
+ "OUTPUT deleted.ScrapReasonID, \n"
+ " inserted.ScrapReasonID, \n"
+ " inserted.WorkOrderID, \n"
+ " inserted.ProductID, \n"
+ " p.Name \n"
+ " INTO @MyTestVar \n"
+ "FROM Production.WorkOrder AS wo \n"
+ " INNER JOIN Production.Product AS p \n"
+ " ON wo.ProductID = p.ProductID \n"
+ " AND wo.ScrapReasonID= 16 \n"
+ " AND p.ProductID = 733",
true);
}
@Test
public void testUpdateSetsIssue1590() throws JSQLParserException {
Update update = (Update) CCJSqlParserUtil.parse("update mytable set a=5 where b = 2");
assertEquals(1, update.getUpdateSets().size());
update.addColumns(new Column("y"));
update.addExpressions(new DoubleValue("6"));
// update.getUpdateSets().get(0).add(new Column("y"), new DoubleValue("6"));
assertEquals("UPDATE mytable SET (a, y) = (5, 6) WHERE b = 2", update.toString());
}
@Test
void testArrayColumnsIssue1083() throws JSQLParserException {
String sqlStr = "SELECT listes[(SELECT cardinality(listes))]";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "update utilisateur set listes[0] = 1";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "update utilisateur set listes[(select cardinality(listes))] = 1";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
sqlStr = "update utilisateur set listes[0:3] = (1,2,3,4)";
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
}
@Test
void testIssue1910() throws JSQLParserException {
Update update = new Update();
update.setTable(new Table("sys_dept"));
UpdateSet updateSet = new UpdateSet(new Column("deleted"), new LongValue(1L));
update.addUpdateSet(updateSet);
TestUtils.assertStatementCanBeDeparsedAs(update, "UPDATE sys_dept SET deleted = 1", true);
updateSet.add(new Column("created"), new LongValue(2L));
TestUtils.assertStatementCanBeDeparsedAs(update,
"UPDATE sys_dept SET (deleted, created) = (1,2)", true);
}
@Test
void testInsertWithinCte() throws JSQLParserException {
String sqlStr = "WITH inserted AS ( " +
" INSERT INTO x (foo) " +
" SELECT bar FROM b " +
" RETURNING y " +
") " +
" UPDATE z " +
" SET foo = 1 " +
" WHERE y IN (SELECT y FROM inserted) ";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(sqlStr);
assertEquals("z", update.getTable().toString());
List<WithItem<?>> withItems = update.getWithItemsList();
assertEquals(1, withItems.size());
Insert insert = withItems.get(0).getInsert().getInsert();
assertEquals("x", insert.getTable().toString());
assertEquals("SELECT bar FROM b", insert.getSelect().toString());
assertEquals(" RETURNING y", insert.getReturningClause().toString());
assertEquals("INSERT INTO x (foo) SELECT bar FROM b RETURNING y", insert.toString());
assertEquals(" inserted", withItems.get(0).getAlias().toString());
}
@Test
void testUpdateWithinCte() throws JSQLParserException {
String sqlStr = "WITH updated AS ( " +
" UPDATE x " +
" SET foo = 1 " +
" WHERE bar = 2 " +
" RETURNING y " +
") " +
" UPDATE z " +
" SET foo = 1 " +
" WHERE y IN (SELECT y FROM inserted) ";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(sqlStr);
assertEquals("z", update.getTable().toString());
List<WithItem<?>> withItems = update.getWithItemsList();
assertEquals(1, withItems.size());
Update innerUpdate = withItems.get(0).getUpdate().getUpdate();
assertEquals("x", innerUpdate.getTable().toString());
assertEquals("foo", innerUpdate.getUpdateSets().get(0).getColumn(0).toString());
assertEquals("1", innerUpdate.getUpdateSets().get(0).getValue(0).toString());
assertEquals("bar = 2", innerUpdate.getWhere().toString());
assertEquals(" RETURNING y", innerUpdate.getReturningClause().toString());
assertEquals(" updated", withItems.get(0).getAlias().toString());
}
@Test
void testDeleteWithinCte() throws JSQLParserException {
String sqlStr = "WITH deleted AS ( " +
" DELETE FROM x " +
" WHERE bar = 2 " +
" RETURNING y " +
") " +
" UPDATE z " +
" SET foo = 1 " +
" WHERE y IN (SELECT y FROM inserted) ";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(sqlStr);
assertEquals("z", update.getTable().toString());
List<WithItem<?>> withItems = update.getWithItemsList();
assertEquals(1, withItems.size());
Delete delete = withItems.get(0).getDelete().getDelete();
assertEquals("x", delete.getTable().toString());
assertEquals("bar = 2", delete.getWhere().toString());
assertEquals(" RETURNING y", delete.getReturningClause().toString());
assertEquals(" deleted", withItems.get(0).getAlias().toString());
}
@Test
void testDeleteAndInsertWithin2Ctes() throws JSQLParserException {
String sqlStr = "WITH deleted AS ( " +
" DELETE FROM x " +
" WHERE bar = 2 " +
" RETURNING y " +
") " +
", inserted AS ( " +
" INSERT INTO x (foo) " +
" SELECT bar FROM b " +
" WHERE y IN (SELECT y FROM deleted) " +
" RETURNING w " +
") " +
" UPDATE z " +
" SET foo = 1 " +
" WHERE y IN (SELECT y FROM inserted) ";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(sqlStr);
assertEquals("z", update.getTable().toString());
List<WithItem<?>> withItems = update.getWithItemsList();
assertEquals(2, withItems.size());
Delete delete = withItems.get(0).getDelete().getDelete();
assertEquals("x", delete.getTable().toString());
assertEquals("bar = 2", delete.getWhere().toString());
assertEquals(" RETURNING y", delete.getReturningClause().toString());
assertEquals(" deleted", withItems.get(0).getAlias().toString());
Insert insert = withItems.get(1).getInsert().getInsert();
assertEquals("x", insert.getTable().toString());
assertEquals("SELECT bar FROM b WHERE y IN (SELECT y FROM deleted)",
insert.getSelect().toString());
assertEquals(" RETURNING w", insert.getReturningClause().toString());
assertEquals(
"INSERT INTO x (foo) SELECT bar FROM b WHERE y IN (SELECT y FROM deleted) RETURNING w",
insert.toString());
assertEquals(" inserted", withItems.get(1).getAlias().toString());
}
@Test
void testSelectAndInsertWithin2Ctes() throws JSQLParserException {
String sqlStr = "WITH selection AS ( " +
" SELECT y " +
" FROM z " +
" WHERE foo = 'bar' " +
") " +
", inserted AS ( " +
" INSERT INTO x (foo) " +
" SELECT bar FROM b " +
" WHERE y IN (SELECT y FROM selection) " +
" RETURNING w " +
") " +
" UPDATE z " +
" SET foo = 1 " +
" WHERE y IN (SELECT y FROM inserted) ";
Update update = (Update) assertSqlCanBeParsedAndDeparsed(sqlStr);
assertEquals("z", update.getTable().toString());
List<WithItem<?>> withItems = update.getWithItemsList();
assertEquals(2, withItems.size());
PlainSelect select = withItems.get(0).getSelect().getPlainSelect();
assertEquals("SELECT y FROM z WHERE foo = 'bar'", select.toString());
assertEquals(" selection", withItems.get(0).getAlias().toString());
Insert insert = withItems.get(1).getInsert().getInsert();
assertEquals("x", insert.getTable().toString());
assertEquals("SELECT bar FROM b WHERE y IN (SELECT y FROM selection)",
insert.getSelect().toString());
assertEquals(" RETURNING w", insert.getReturningClause().toString());
assertEquals(
"INSERT INTO x (foo) SELECT bar FROM b WHERE y IN (SELECT y FROM selection) RETURNING w",
insert.toString());
assertEquals(" inserted", withItems.get(1).getAlias().toString());
}
@ParameterizedTest
@ValueSource(strings = {
"UPDATE mytable SET mycolumn1 = mycolumn2 PREFERRING HIGH mycolumn",
"UPDATE mytable SET mycolumn1 = mycolumn2 PREFERRING LOW mycolumn",
"UPDATE mytable SET mycolumn1 = mycolumn2 PREFERRING 1 = 1",
"UPDATE mytable SET mycolumn1 = mycolumn2 PREFERRING (HIGH mycolumn)",
"UPDATE mytable SET mycolumn1 = mycolumn2 PREFERRING INVERSE (HIGH mycolumn)",
"UPDATE mytable SET mycolumn1 = mycolumn2 PREFERRING HIGH mycolumn1 PRIOR TO LOW mycolumn2",
"UPDATE mytable SET mycolumn1 = mycolumn2 PREFERRING HIGH mycolumn1 PLUS LOW mycolumn2"
})
public void testPreferringClause(String sqlStr) throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(sqlStr);
}
@Test
public void testUpdateWithBoolean() throws JSQLParserException {
String statement = "UPDATE mytable set col1='as', col2=true Where o >= 3";
Update update = (Update) PARSER_MANAGER.parse(new StringReader(statement));
assertEquals("mytable", update.getTable().toString());
assertEquals(2, update.getUpdateSets().size());
assertEquals("col1", update.getUpdateSets().get(0).getColumns().get(0).getColumnName());
assertEquals("col2", update.getUpdateSets().get(1).getColumns().get(0).getColumnName());
assertEquals("as",
((StringValue) update.getUpdateSets().get(0).getValues().get(0)).getValue());
assertInstanceOf(BooleanValue.class, update.getUpdateSets().get(1).getValues().get(0));
assertTrue(((BooleanValue) update.getUpdateSets().get(1).getValues().get(0)).getValue());
assertInstanceOf(GreaterThanEquals.class, update.getWhere());
}
@Test
public void testUpdateWithSkylineKeywords() throws JSQLParserException {
String statement =
"UPDATE mytable SET low = 1, high = 2, inverse = 3, plus = 4 WHERE id = 6";
Update update = (Update) PARSER_MANAGER.parse(new StringReader(statement));
assertEquals("mytable", update.getTable().toString());
assertEquals(4, update.getUpdateSets().size());
assertEquals("low", update.getUpdateSets().get(0).getColumns().get(0).getColumnName());
assertEquals("high", update.getUpdateSets().get(1).getColumns().get(0).getColumnName());
assertEquals("inverse", update.getUpdateSets().get(2).getColumns().get(0).getColumnName());
assertEquals("plus", update.getUpdateSets().get(3).getColumns().get(0).getColumnName());
assertInstanceOf(EqualsTo.class, update.getWhere());
}
}