尝试在一个表中创建列并从另一个表设置其值

I tried to add a new column to a table in same db , I then tried to copy new column from another table's column.

我试图在同一个数据库中的表中添加一个新列,然后我尝试从另一个表的列中复制新列。

        ALTER TABLE [library].[dbo].[Member$]
        add  new_column1 float
        go
        update [library] .[dbo] .[Member$] 
        set new_column1  = select [library].[dbo].Category$ .Category  from  [library] .[dbo] .[Category$]  
        GO
    CREATE TABLE [dbo].[Category$](
        [CatID] [float] NULL,
        [Category] [nvarchar](255) NULL,
        [BooksLimit] [float] NULL,
        [Period] [float] NULL,
        [FinePerDay] [float] NULL
    ) ON [PRIMARY]

    GO

CREATE TABLE [dbo].[Member$](
    [Roll No] [float] NULL,
    [RollNo] [nvarchar](255) NULL,
    [Name] [nvarchar](255) NULL,
    [CatID] [nvarchar](255) NULL,
    [ExpiryDate] [datetime] NULL,
    [ExpiryReason] [nvarchar](255) NULL,
    [Session] [nvarchar](255) NULL,
    [new_column] [float] NULL,
    [new_column1] [float] NULL
) ON [PRIMARY]

but the new_column1 is still null

但是new_column1仍为null

there are multiple values in Category , but row number in both doesnot match, Is that a problem , If yes then how can I acheive this ?

类别中有多个值,但两者中的行号不匹配,这是一个问题,如果是,那我该如何实现呢?

1 个解决方案

#1

Try this:

ALTER TABLE [library].[dbo].[Member$] add new_column1 float 
go 
update [library] .[dbo] .[Member$] set 
  new_column1 = 
  (
    select TOP 1 [library].[dbo].[Category$].Category 
    from [library].[dbo].[Category$]
  )

BUT

Doing so you just assigns the same value to all the rows in table Member$, if you need specific settings for each Member$'s row - try to place proper filtering in the subquery

这样做你只需为表Member $中的所有行分配相同的值,如果你需要为每个Member $行设置特定的设置 - 尝试在子查询中放置适当的过滤

select TOP 1 [library].[dbo].[Category$].Category 
from [library].[dbo].[Category$]
WHERE PLACE_YOUR_FILTER_HERE

like this:

ALTER TABLE [library].[dbo].[Member$] add new_column1 float 
go 
update [library] .[dbo] .[Member$] set 
  new_column1 = 
  (
    select TOP 1 [library].[dbo].[Category$].Category 
    from [library].[dbo].[Category$]
    WHERE [library] .[dbo] .[Member$].CatId = [library].[dbo].[Category$].CatId
  )

BUT, special note

但是,特别说明

If you want to use ID for category - better use for it an exact type, such as int - because FLOAT types family are not exact and thus - harder to compare.

如果你想使用ID作为类别 - 更好地使用它作为一个确切的类型,例如int - 因为FLOAT类型族不精确,因此 - 比较难。

#1