2

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

Edit: 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.

Improve this question
1
  • And that SET anamoly_id wrong column name is a typo - right?? Is it that column in your target table really called something else than in the source table (you said in the beginning the column names were identical.....) Commented Jan 22, 2012 at 9:52

5 Answers 5

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Rauschen.It was so simple,and i was wrappinf my head around it bad.Question answered
You can mark the answer as "accepted" than it will be shown as first answer under you question .. and other see that the problem is solved
@user1151462: This thread will help you to see how, when, why to accept an answer and what answer to accept: How does accepting an answer work?
0

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 ..

Improve this answer

4 Comments

I am new to sql i just want to copy data from a filled column to a empty one on the next table
I understand that but currently you have two filled columns .. please describe the table structure better ... Or maybe an INSERT is what you want
table 1 stu_id PID anamoly_id ||table 2 PID Anomaly_id||Table 1(amb.anamoly.fee is full and Table 2 (amb.anamoly_log_update) is empty.I want to copy all the data from anamoly ID in table 1 to the anamoly_id column in table 2
can you please update the question with tables schemas? I don't understand your previous comment
0

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.

Improve this answer

3 Comments

actually no, 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.
INSERT INTO anamoly_log_update (anamoly_id) SELECT anamoly_id FROM anamoly_fee
@user1151462 Please accept the answer, if it served the purpose
0

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

Improve this answer

Comments

0 
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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.