How to insert value using a table in another table in postgres?

Question

CREATE TABLE employee
(
    joining_date date,
    employee_type character varying,
    name character varying 
);
insert into employee VALUES
    (NULL,'as','hjasghg'),
    ('2022-08-12', 'Rs', 'sa'),
    (NULL,'asktyuk','hjasg');
    
create table insrt_st (employee_type varchar, dt date);
insert into insrt_st VALUES ('as', '2022-12-01'),('asktyuk', '2022-12-08')

What I want to do is write a single query to insert date from insrt_st in employee table where joining_date is null in employee column?

It's unclear to me what you are trying to achieve. Can't you just run an UPDATE statement after the two INSERTs? — user330315
– user330315, Commented Jan 11, 2023 at 13:00
you mean this? dbfiddle.uk/xDXPOJm2 add the expected results to make your question clearer. cheers — Jim Jones
– Jim Jones, Commented Jan 11, 2023 at 13:13
@a_horse_with_no_name, this is a sample. I have more than a thousand such columns. _gotqn provided an appropriate answer. — codeanonym
– codeanonym, Commented Jan 11, 2023 at 13:30

gotqn · Accepted Answer · 2023-01-11 13:12:15Z

1

You can just write:

insert into employee (joining_date, employee_type, name) VALUES 
    (NULL,'as','hjasghg'),
    ('2022-08-12', 'Rs', 'sa'),
    (NULL,'asktyuk','hjasg');
    
insert into insrt_st (employee_type, dt)
VALUES ('as', '2022-12-01')
     ,('asktyuk', '2022-12-08') ;


UPDATE employee
SET joining_date = I.dt
FROM insrt_st I
WHERE employee.employee_type = I.employee_type 
AND employee.joining_date IS NULL;

SELECT joining_date,employee_type,name
FROM employee

Here is example.

edited Jan 11, 2023 at 13:12

answered Jan 11, 2023 at 12:35

gotqn

44k47 gold badges167 silver badges256 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert value using a table in another table in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related