21990

SQL Server: copy data from one column to another column?

I have two tables with the same column anomaly_id. I want to copy the row of anomaly_id from the first table to the second table using this code

UPDATE amb.anamoly_log_update SET anamoly_id = t2.anomaly_id FROM amb.anamoly_log_update t1 INNER JOIN amb.anomaly_fee t2 ON t1.anamoly_id=t2.anomaly_id

Even after I did that it shows 0 rows affected, when there is data in amb.anomaly.fee (source table)

Please help

<strong>Edit:</strong> Comment from post: I just want to copy all the anamoly_id from amb.anamoly_fee to amb.anamoly_log_update. My code might be nonsensical. Please do review it.

Answer1:

To copy the id from anomaly_fee to anamoly_log_update use :

INSERT INTO anamoly_log_update (anamoly_id) SELECT anamoly_id FROM anomaly_fee

with both columns it looks like that:

INSERT INTO anamoly_log_update (anamoly_id,PID) SELECT anamoly_id,PID FROM anomaly_fee

Answer2:

You only would copy the data if they where in both tables .. and then there is nothing update because you do not change the data => 0 rows affected

ON t1.anamoly_id=t2.anomaly_id

please think about what you really want to do and change your description ..

Answer3:

Does amb.anamoly_log_update contain at least one row corresponding to the anamoly_id that's present in amb.anamoly_fee? You are trying to join on two tables on anamoly_id.

Answer4:

You need to provide other linkage between tables than t1.anamoly_id=t2.anomaly_id or the query will do nothing

Answer5:

merge into amb.anamoly_log_update as t1 using amb.anomaly_fee as t2 on t1.anamoly_id=t2.anomaly_id when matched then update set t1.anamoly_id = t2.anomaly_id

Recommend

  • JFrame with blurred transparent background
  • What resources are needed to start SMS programming through Java? [closed]
  • Error in rounding off values using .round in Ruby
  • How to make sure string has double quotes?
  • does the one thread kill the other in a iphone application( both thread are main thread)?
  • Performance Issue with TChart
  • Wordpress: How to create a custom search form only to a specific page
  • Foreign Key Error on MySQL CREATE TABLE statement (err: 150)
  • Error Firebase Configuration in android studio
  • mySQL INSERT IGNORE doesn't “ignore”
  • replace up to nth match
  • Row is not being deleted because of cascade trigger updates this row
  • Problem with AutoCompleteTextView and Spinner using the same Adapter
  • Checked exception specification and strategy pattern
  • Differentiate between 'no rows were affected' and rows succesfully UPDATEd--to same value
  • Does setting autocommit=0 within a transaction do anything?
  • wait function that uses while
  • How to select all local links in jQuery
  • Migration of Google Project to Firebase Console
  • What drive the natural result order for an unordered MySQL request
  • TypeScript Mapped Types: Get element type of array
  • I have a SQLite syntax error in DELETE statement
  • Unselect column after pasting data
  • Edge-case: When (only) reversing order of template parameters in specialization, can non-specialized
  • How to avoid or stop multiple ajax request
  • Django: Count of Group Elements
  • Controls, properties, events and timers running in design time
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • vba code to select only visible cells in specific column except heading
  • Updated Ionic CLI but shows previous version (Windows)
  • Javascript + PHP Encryption with pidCrypt
  • Websockets service method fails during R startup
  • Do I've to free mysql result after storing it?
  • How to get next/previous record number?
  • Transpose CSV data with awk (pivot transformation)
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Sorting a 2D array using the second column C++