I have a requirement in which I need to get one column from another table and insert that column data with some other data into another table.
Example:
If the cust_id='11' then I need to get the cust_code from cust table (let's say it returns cust_code='ABCD') and then use that cust_code with some other data to insert into table_1 like so:
WITH get_cust_code_for_cust_id AS (
SELECT cust_code FROM cust WHERE cust_id=11
)
INSERT INTO public.table_1(
cust_code, issue, status, created_on)
VALUES (SELECT cust_code FROM get_cust_code_for_cust_id, 'New Issue', 'Open', current_timestamp)
But this query does not work as we haven't called the get_cust_code_for_cust_id
query.
My preference is some query with WITH
clause but any other answer will also be appreciated.
If the source of an insert
statement is a select
do not use the VALUES
keyword.
WITH get_cust_code_for_cust_id AS (
SELECT cust_code
FROM cust
WHERE cust_id=11
)
INSERT INTO public.table_1 (cust_code, issue, status, created_on)
SELECT cust_code, 'New Issue', 'Open', current_timestamp
FROM get_cust_code_for_cust_id;
You don't really need the CTE for this though:
INSERT INTO public.table_1 (cust_code, issue, status, created_on)
SELECT cust_code, 'New Issue', 'Open', current_timestamp
FROM cust
WHERE cust_id=11