如何在同一个表中将数据从一列复制到另一列？ [英] How can I copy data from one column to another in the same table?

问题描述

我正在尝试将列复制到同一个表中的另一个cloumn。

i am trying to copy column to another cloumn in the same table.

< span style ="color：＃0066cc">更新客户设置[phoneNo] = [CellNo]？

update customers set [phoneNo]=[CellNo]?

推荐答案

是您使用TableAdapter，DataAdapter，托管数据提供程序，例如OleDb等???

Are you using a TableAdapter, DataAdapter, a managed data provider e.g. OleDb etc ???

这是一个SQL-Server的例子，我首先拿了一个样本表，用于回答这里的问题，并添加了手机和单元格。

Here is an example for SQL-Server where I first took a sample table I use for answering question here and added phone and cell.

撰写如下更新，主键等于2，然后立即使用SQL Server Management Studio中的SELECT检查结果。

Wrote an update as follows which seeks the primary key equaling 2, then immediately check the results with a SELECT in SQL Server Management Studio.

UPDATE  p
SET     p.CellNumber = p.PhoneNumber
FROM    dbo.PeopleDemo AS p
        JOIN dbo.PeopleDemo AS pd ON p.PK = pd.PK
WHERE   p.FirstName = 'Mary';
SELECT  *
FROM    dbo.PeopleDemo AS pd
WHERE   pd.PK = 2;

 

使用System.Data.SqlClient进行上述更新并将其放入下面的代码中

 

Took the above UPDATE and place it into the code below

using System.Data.SqlClient;

namespace WindowsFormsApplication3
{
    public class Operations
    {
        string ConnectionString = "Data Source=KARENS-PC;" + 
                "Initial Catalog=ForumExamples;Integrated Security=True";

        public void FullName(int pId)
        {
            using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
            {
                using (SqlCommand cmd = new SqlCommand { Connection = cn })
                {

                    cmd.Parameters.AddWithValue("@pk", pId);
                    cmd.CommandText = "UPDATE p SET p.CellNumber = p.PhoneNumber FROM dbo.PeopleDemo AS p " + 
                                      "JOIN dbo.PeopleDemo AS pd ON p.PK = pd.PK WHERE p.pk = @pk;";
                    cn.Open();
                    var result = cmd.ExecuteNonQuery();
                    if (result == 1)
                    {
                        // success
                    }
                    else
                    {
                        // failed
                    }
                }
            }
        }
    }
}

或

using System.Data.SqlClient;

namespace WindowsFormsApplication3
{
    public class Operations
    {
        string ConnectionString = "Data Source=KARENS-PC;" + 
                "Initial Catalog=ForumExamples;Integrated Security=True";

        public void FullName(int pId)
        {
            using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
            {
                using (SqlCommand cmd = new SqlCommand { Connection = cn })
                {

                    cmd.Parameters.AddWithValue("@pk", pId);
                    cmd.CommandText = "UPDATE dbo.PeopleDemo SET CellNumber = PhoneNumber WHERE Pk = @Pk";
                    cn.Open();
                    var result = cmd.ExecuteNonQuery();
                    if (result == 1)
                    {
                        // success
                    }
                    else
                    {
                        // failed
                    }
                }
            }
        }
    }
}

在一个名为上述方法的表格中

In a form called the method above

using System;
using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
       
        private void button1_Click(object sender, EventArgs e)
        {
            Operations ops = new Operations();
            ops.FullName(2);
        }
    }
}

如果result = 1我们知道成功，否则我们失败了。这有意义吗？

We know success if result = 1, otherwise we failed. Does this make sense?

最后，如果你失去了WHERE条件，所有行都会受到UPDATE的影响。

Lastly, if you lose the WHERE condition all rows will be affected by the UPDATE.

这篇关于如何在同一个表中将数据从一列复制到另一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！