带SELECT的Sql INSERT [英] Sql INSERT with SELECT

问题描述

我有这个更新table1.Table1的查询有另一个名为OldReference的列，这个查询被设置为NULL，现在我需要将此列设置为NewReference的值，然后NewRefernce将是根据选择的查询设置。

Hi, I have this query that is updating a table1.Table1 has another column called OldReference with this query it is being set to NULL, now I need to set this column as the value of the NewReference where then the NewRefernce will be set based on the select query.

INSERT INTO [Table1] (
    Class, DateAdded, User, [Type], NewReference
) (
    SELECT Class_ID, GetDate(), @User_ID, table2_id, @Current_Lot
    FROM Table2
        CROSS JOIN @StripList
    WHERE MType = 'Add New'
)

我尝试过：

What I have tried:

INSERT INTO [Table1] (
    Class, DateAdded, User, [Type], OldReference, NewReference
) (
    SELECT Class_ID, GetDate(), @User_ID, table2_id, T1.OldReference, @Current_Lot
    FROM Table1 T1, Table2
        CROSS JOIN @StripList
    WHERE MType = 'Add New'
)

推荐答案

UPDATE T1
   SET DateAdded=GETDATE() --I believe you need a field for UpdateDate
    , User=@USER_ID
    ,Type=table2_id
    ,OldReference =t1.newReference
    ,NewReference=@Current_Lot
FROM Table1 T1, Table2
        CROSS JOIN @StripList
    WHERE MType = 'Add New'

这篇关于带SELECT的Sql INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！