简单插入更新查询 [英] simple insert update query

问题描述

insert into employees(employee_id,last_name,hire_date,job_id,email)
values(dep,'a',sysdate,'1','www')
select department_id as dep  from departments where manager_id=200;

i希望将department_id值插入到员工ID和休息列中作为默认值...我正在变量...你可以建议

i want to insert department_id value into employee id and rest columns as default... i am getting wrrr.. can u plz suggest

推荐答案

试试这个

Try this

insert into employees(employee_id,last_name,hire_date,job_id,email)
select department_id, 'a', sysdate, '1', 'www'  from departments where manager_id=200;

将相同的部门ID查询放在您放置部门的位置。

你将要做的是使用子查询。

Put the same department id query in the location where you have put dept.
What you will basically be doing is using a subquery.

实现这一点的方法很少：

There are few ways to achieve that:

1. 使用变量
   
   

1. using variable
   

DECLARE @dep INT
SELECT @dep = department_id 
FROM departments 
WHERE manager_id=200;
INSERT INTO employees(dept_id, last_name, hire_date, job_id, email)
VALUES(@dep,'a',sysdate,'1','www')

使用存储过程 [ ^ ]

using stored procedure[^]

CREATE PROCEDURE InsertNewEmployee
    @manid INT,
    @lname VARCHAR(50),
    @jobid INT,
    @email VARCHAR(50)
AS
BEGIN
    DECLARE @dept INT
    SELECT @dept = COALESCE(department_id,0)
    FROM departments
    WHERE manager_id = @manid

    IF @dept >0 
    BEGIN
        INSERT INTO employees(dept_id, last_name, hire_date, job_id, email)
        VALUES(@dept,@lname,sysdate(),@jobid,@email)
    ELSE
        PRINT 'Unknown manager!'
    END

END

这篇关于简单插入更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！