How do I insert into a third table using a where statement comparing table a and table B

Question

0.00/5 (No votes)

See more:

What I am trying to accomplish is having a reference/source table we will call (C) that the data gets inserted into after table (A) gets updated from Table (B)

update table A where table B.col1 = A.col1 and B.col2 = A.col2
Set A.col3= B.col3 and A.col4 = B.col4

insert into C (Source_ID, source_reference) where B.col1= A.col1 and B.col2 = A.col2

then delete matched sources from table B to get rid of duplicates.

What I have tried:

I have tried to use a match and merge statement, however, I cannot use a match merge statement to transfer to a source table for references later.

Posted 3-Aug-18 8:06am

Member 11856456

Updated 4-Aug-18 3:42am

v2

Add a Solution

Comments

[no name] 3-Aug-18 17:56pm

From where you learned this rubbish SQL update Statement like this (note the ??):

update table A where table B.col1 = A.col1 and B.col2 = A.col2
Set
A.col1= B.col1
?? and A.col2 = B.col2 ??

Member 11856456 4-Aug-18 9:43am

Sorry, I realized after your question that I duplicated the columns. I have updated the question.

[no name] 4-Aug-18 9:54am

No Need to be sorry. Anyway I don't see a big difference. From my Point of view you Need to fix first the update Statement:

update table A 
 Set A.col3= B.col3,
     A.col4 = B.col4
where table B.col1 = A.col1 and B.col2 = A.col2

[no name] 4-Aug-18 10:48am

Your question is (at least for me) very unclear. Apart from that your insert Statement is syntax wise wrong; it is completely unrelated to the Prior update...

Santosh kumar Pithani 4-Aug-18 0:57am

why your using same columns "B.col1 = A.col1 and B.col2 = A.col2" for condition as well as update set?

Member 11856456 4-Aug-18 9:11am

I was giving an example of what I am trying to accomplish. I am trying to make a match statement without using the match and merge. I am trying to achieve this because match and merge will not insert in a third table which is what I need. As for the columns I want to find equivalencies between tables A and B and the information that is not filled in I want to update that information from table B.

RedDk 5-Aug-18 11:58am

In earnest, improving your question ... is something we need to do in this case. So, here we go. Forget that idea ... I have a question for you. Why don't you show your TSQL query along with some CREATE TABLE code so we can throw some data in there in order to spelunk the error code you're seeing when you run the faulty code. Oh, wait ... FOrget that idea. Just give us your code, we'll slap some data into the table and ... what is the error code? Exactly.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)