locked
copy data from one table to another RRS feed

  • Question

  • User-1106823036 posted

    I want to copy data from one table to another

    I have table Item(Id- itemName-publisher_Id, Publisher_Name)

    another table publisher (Publisher_Id,Publisher_Name)

    I want to copy the publisher_Id to table Item based on Publisher_Name

    so I used this query

    UPDATE Item set Publisher_Id=(select Publisher_Id from publisher where Item.Publisher_Name=Active_Publisher.Publisher_Name)

    I get this error

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Note: some Publisher_Name in the table Item doesn't exist in the table publisher so ID will be null, no problem with null values for Publisher_Id in table Item

    Thursday, January 28, 2016 9:37 AM

Answers

  • User177399542 posted
    UPDATE Item set Publisher_Id=(select DISTINCT Publisher_Id from publisher where Item.Publisher_Name=Active_Publisher.Publisher_Name)

    or

    UPDATE Item set Publisher_Id=(select TOP 1 Publisher_Id from publisher where Item.Publisher_Name=Active_Publisher.Publisher_Name)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 28, 2016 10:06 AM
  • User-698989805 posted

    Do the following:

    UPDATE Test1.dbo.Employee SET DeptID = emp2.DeptID FROM Test2.dbo.Employee as 'emp2' WHERE Test1.dbo.Employee.EmployeeID = emp2.EmployeeID

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 28, 2016 10:15 AM

All replies

  • User177399542 posted
    UPDATE Item set Publisher_Id=(select DISTINCT Publisher_Id from publisher where Item.Publisher_Name=Active_Publisher.Publisher_Name)

    or

    UPDATE Item set Publisher_Id=(select TOP 1 Publisher_Id from publisher where Item.Publisher_Name=Active_Publisher.Publisher_Name)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 28, 2016 10:06 AM
  • User-698989805 posted

    Do the following:

    UPDATE Test1.dbo.Employee SET DeptID = emp2.DeptID FROM Test2.dbo.Employee as 'emp2' WHERE Test1.dbo.Employee.EmployeeID = emp2.EmployeeID

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 28, 2016 10:15 AM
  • User-986267747 posted

    Hi lolo512,

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

    According to the error message, when the value of the Publisher_Name equals the value of, the following query will return multiple rows that are identical, so you get the error message. You need to use DISTINCT  to filter your data so that it only return one record

    select Publisher_Id from publisher where Item.Publisher_Name=Active_Publisher.Publisher_Name

    Besides, I'm not sure if the query is your completed query string, i think you couldn't directly use Active_Publisher.Publisher_Name to access the data in Active_Publisher,

    Best Regards,

    Klein zhang

    Friday, January 29, 2016 6:57 AM