Skip to main content

From

Definition​

The from methods allows you to add the FROM clause to the query.

Available methods​

1. from(KTable kTable)​

  • kTable: is the table which will be added to FROM clause.

2. from(KRaw kRaw)​

  • kRaw: is a raw content which will be added in the FROM clause.

3. from(KCommonTableExpressionFilled kCommonTableExpressionFilled)​

  • kCommonTableExpressionFilled: is a Common Table Expressions or CTE that will be added to the FROM clause.

Method hierarchy​

The from method can be used right after the following methods:

and the subsequent methods that can be called are:

Example: KTable (generated.metadata)​

Java code:

k
.update(APP_USER)
.set(APP_USER.FIRST_NAME, APP_USER.LAST_NAME)
.from(ROLE)
.where(APP_USER.ROLE_ID.eq(ROLE.ID))
.and(ROLE.ID.eq(7L))
.execute();

SQL generated:

UPDATE app_user au
SET first_name = au.last_name
FROM role ro
WHERE au.role_id = ro.id
AND ro.id = ?1

Parameters:

  • ?1: 7

Example: KTable (from subquery)​

Java code:

final KTable subquery =
    k
    .select(ROLE.ID)
    .from(ROLE)
    .where(ROLE.ID.eq(7L))
    .as("r");

k
.update(APP_USER)
.set(APP_USER.FIRST_NAME, APP_USER.LAST_NAME)
.from(subquery)
.where(APP_USER.ROLE_ID.eq(subquery.c("id")))
.execute();

SQL generated:

UPDATE app_user au
SET first_name = au.last_name
FROM (
    SELECT ro.id
    FROM role ro
    WHERE ro.id = ?1
) r
WHERE au.role_id = r.id

Parameters:

  • ?1: 7

Example: KTable (from subquery with custom aliases in a tuple)​

Java code:

final KTable subquery =
    k
    .select(ROLE.ID, ROLE.NAME)
    .from(ROLE)
    .where(ROLE.ID.eq(7L))
    .as("r", "a", "b");

k
.update(APP_USER)
.set(APP_USER.FIRST_NAME, subquery.c("b"))
.from(subquery)
.where(APP_USER.ROLE_ID.eq(subquery.c("a")))
.execute();

SQL generated:

UPDATE app_user au
SET first_name = r.b
FROM (
    SELECT ro.id
    FROM role ro
    WHERE ro.id = ?1
) r (a, b)
WHERE au.role_id = r.a

Parameters:

  • ?1: 7

Example: KRaw​

Java code:

k
.update(APP_USER)
.set(APP_USER.FIRST_NAME, APP_USER.LAST_NAME)
.from(raw("role ro"))
.where(raw("au.role_id = ro.id"))
.and(ROLE.ID.eq(7L))
.execute();

SQL generated:

UPDATE app_user au
SET first_name = au.last_name
FROM role ro
WHERE au.role_id = ro.id
AND ro.id = ?1

Parameters:

  • ?1: 7

Example: KCommonTableExpressionFilled​

Java code:

final KValues userIdsValues =
    values()
    .append(new ArrayList<>() {{
        add(10605L);
    }})
    .append(new ArrayList<>() {{
        add(13L);
    }});

final KCommonTableExpressionFilled userIdsCte =
    cte("user_ids_cte")
    .columns("id")
    .as(userIdsValues, "uic");

k
.with(userIdsCte)
.update(APP_USER)
.set(APP_USER.FIRST_NAME, APP_USER.LAST_NAME)
.from(userIdsCte)
.where(userIdsCte.c("id").eq(APP_USER.ID))
.execute();

SQL generated:

WITH user_ids_cte (id) AS (
    VALUES (?1), (?2)
) 
UPDATE app_user au
SET first_name = au.last_name
FROM user_ids_cte uic
WHERE uic.id = au.id

Parameters:

  • ?1: 10605
  • ?2: 13