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

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?

CodePudding user response：

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.