VALUES

This section outlines the process for creating a temporary table using the VALUES statement in Jet.

SQL dialects:

• Postgres/CockroachDB
• MySQL
• SQLite
• MariaDB

Postgres/CockroachDB

Using sub-query

paymentID := IntegerColumn("payment_ID")
increase := FloatColumn("increase")

values := VALUES(
	WRAP(Int32(20564), Double(1.21)),
	WRAP(Int32(20567), Double(1.02)),
	WRAP(Int32(20570), Double(1.34)),
	WRAP(Int32(20573), Double(1.72)),
).AS("values", paymentID, increase)

stmt := SELECT(Payment.AllColumns).
	FROM(Payment.INNER_JOIN(values, paymentID.EQ(Payment.PaymentID))).
	WHERE(increase.GT(Double(1.03)))

Using CTE

paymentID := IntegerColumn("payment_ID")
increase := FloatColumn("increase")
paymentsToUpdate := CTE("values_cte", paymentID, increase)

stmt := WITH(
	paymentsToUpdate.AS(
		VALUES(
			WRAP(Int32(20564), Double(1.21)),
			WRAP(Int32(20567), Double(1.02)),
			WRAP(Int32(20570), Double(1.34)),
			WRAP(Int32(20573), Double(1.72)),
		),
	),
)(
	Payment.UPDATE().
	SET(
		Payment.Amount.SET(Payment.Amount.MUL(increase)),
	).
	FROM(paymentsToUpdate).
	WHERE(Payment.PaymentID.EQ(paymentID)).
	RETURNING(Payment.AllColumns),
)

Important

While the ROW constructor creates a new row expression, it is not a suitable replacement for WRAP in the VALUES context.
When specifying elements for VALUES rows,Int or Float constructors can't be used, as they do not add explicit type casting. Instead, use more specific types such as Int32, Int64, Real, Double, etc..

MySQL

Using sub-query

paymentID := IntegerColumn("payment_id")
increase := FloatColumn("increase")

values := VALUES(
	ROW(Int32(204), Float(1.21)),
	ROW(Int32(207), Float(1.02)),
	ROW(Int32(200), Float(1.34)),
	ROW(Int32(203), Float(1.72)),
).AS("values", paymentID, increase)

stmt := SELECT(Payment.AllColumns).
	FROM(Payment.INNER_JOIN(values, paymentID.EQ(Payment.PaymentID))).
	WHERE(increase.GT(Float(1.03)))

Using CTE

paymentID := IntegerColumn("payment_id")
increase := FloatColumn("increase")
paymentsToUpdate := CTE("values_cte", paymentID, increase)

stmt := WITH(
	paymentsToUpdate.AS(
		VALUES(
			ROW(Int32(204), Float(1.21)),
			ROW(Int32(207), Float(1.02)),
			ROW(Int32(200), Float(1.34)),
			ROW(Int32(203), Float(1.72)),
		),
	),
)(
	Payment.INNER_JOIN(paymentsToUpdate, paymentID.EQ(Payment.PaymentID)).
		UPDATE().
		SET(
			Payment.Amount.SET(Payment.Amount.MUL(increase)),
		).WHERE(Bool(true)),
)	

SQLite

Using sub-query

values := VALUES(
	ROW(Int32(204), Float(1.21)),
	ROW(Int32(207), Float(1.02)),
	ROW(Int32(200), Float(1.34)),
	ROW(Int32(203), Float(1.72)),
).AS("values")

paymentID := IntegerColumn("column1")
increase := FloatColumn("column2")

stmt := SELECT(Payment.AllColumns).
	FROM(Payment.INNER_JOIN(values, paymentID.EQ(Payment.PaymentID))).
	WHERE(increase.GT(Float(1.03)))

Note

SQLite does not support direct column aliasing when VALUES is used as subquery. To reference VALUES columns, use the default column names: column1, column2, columnN..., as in the example above. When column aliasing is required, consider using a CTE approach.

Using CTE

paymentID := IntegerColumn("payment_ID")
increase := FloatColumn("increase")
paymentsToUpdate := CTE("values_cte", paymentID, increase)

stmt := WITH(
	paymentsToUpdate.AS(
		VALUES(
			ROW(Int32(204), Float(1.21)),
			ROW(Int32(207), Float(1.02)),
			ROW(Int32(200), Float(1.34)),
			ROW(Int32(203), Float(1.72)),
		),
	),
)(
	Payment.UPDATE().
		SET(
			Payment.Amount.SET(Payment.Amount.MUL(increase)),
		).
		FROM(paymentsToUpdate).
		WHERE(Payment.PaymentID.EQ(paymentID)).
		RETURNING(Payment.AllColumns),
)

MariaDB

In MariaDB, VALUES statements do not function correctly when all elements are placeholders. This requires user to implement some of the workaround solutions.

Workaround using RawStatement

paymentID := IntegerColumn("payment_id")
increase := FloatColumn("increase")
paymentsToUpdate := CTE("values_cte", paymentID, increase)

stmt := WITH(
	paymentsToUpdate.AS(
		RawStatement(`
			 VALUES (204, 1.21),
				(207, 1.02),
				(200, 1.34),
				(203, 1.72)
		`),
	),
)(
	SELECT(
		Payment.AllColumns,
		paymentsToUpdate.AllColumns(),
	).FROM(
		Payment.INNER_JOIN(paymentsToUpdate, paymentID.EQ(Payment.PaymentID)),
	).WHERE(
		increase.GT(Float(1.03)),
	).ORDER_BY(
		increase,
	),
)