Find and Copy Data from Another Table

Question

Find and Copy Data from Another Table

uknites

SSC Veteran

Points: 255
More actions
February 12, 2020 at 8:52 pm
Go to Answer
#3725383

Hi:
I want to update DOB in Customers table from Info Table
Two tables
Customers
ID, Name, DOB, Blood Type
Info
ID, DOB
Scenario
========
Find where ID of “Customers” = ID of “Info” then copy “DOB” from Info to “DOB” Customers
Note: there is data already in DOB of Customers, i want to update it from the data in Info
Thanks
Steve Collins

SSCrazy

Points: 2068
More actions
February 13, 2020 at 6:06 pm
Answer
#3725703
Sorry I didn't look to see this was about Access. The following should work as a Pass-Through query:
```
UPDATE Items INNER JOIN Customers ON Items.ID = Customers.ID SET Customers.DOB = [Items].[DOB];
```
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2068 More actions · Answer 1

update c
set
  dob=i.dob
from
  customers c
 join
  info i on c.id=i.id;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

uknites SSC Veteran Points: 255 More actions · Answer 2

sorry i am not able to decode c and customers c?

info i ?

can you please clerify

thanks a lot

uknites SSC Veteran Points: 255 More actions · Answer 3

uknites

SSC Veteran

Points: 255

February 13, 2020 at 7:17 pm

#3725725

thanks alot 🙂

It worked 🙂

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

uknites wrote:

thanks alot 🙂
It worked 🙂

Excellent. My next question would be... why are you denormalizing/duplicating data? For that matter, why does the INFO table have a DOB column to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 5

You never read the part of the start of every SQL forum is posted for the last 30 years about providing DDL when you post, did you? You also believe that there is such a thing as a generic "id", generic "name" and you're not willing to put the constraint on blood types (the only column whose name you got right). And you do not provde sample data.

CREATE TABLE Customers

(customer_id CHAR(16) NOT NULL PRIMARY KEY,

customer_name NVARCHAR(35) NOT NULL,

birth_date DATE, -- note the NULL!

blood_type CHAR(2) NOT NULL

CHECK (blood_type IN ('A', 'B', 'AB', 'O')),

..);

I would have left the birth date as a null. Otherwise, there are some problems with doing an update off of the second table.

CREATE TABLE Customer_Corrections

(customer_id CHAR(16) NOT NULL PRIMARY KEY,

birth_date DATE NOT NULL,

>> Find where ID of “Customers” = ID [sic] of “Info” then copy “birth_date” from Info to “birth_date” Customers <<

I hope you understand that "customers" is the name of the table, not a column, and that you simply screwed up. This is a simple use of the merge statement, which you should Google.

MERGE INTO Customers AS Target

USING Customer_Corrections AS Source

ON Target.customer_id = Source.customer_id

WHEN MATCHED

THEN Target.birth_date = Source.birth_date;

Please post DDL and follow ANSI/ISO standards when asking for help.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

Jeff, it sounds like the OP has a permanent table Customers, and Info is a staging table that has updates & corrections.

Joe, to quote from Kenneth Fisher's blog yesterday "Is it really that hard to be polite, kind and respectful of those around you?"

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

WILLIAM MITCHELL wrote:

Jeff, it sounds like the OP has a permanent table Customers, and Info is a staging table that has updates & corrections.

Understood. The OP said as much in the last line of his original post. I'm hoping to hear from the OP because this just doesn't sound right. I've never heard of anyone having to update all of the DOBs for all the entries in a Customer table. Something else is wrong and the OP isn't telling us about that. If he did, we might be able to help prevent the need for doing such a thing in the future.

For example, did someone try to update the DOB for a single customer and forget a WHERE clause and they restored a copy of the database get a copy of the Customer table so that they could update the original? That would also let us know that a simple full table UPDATEN would need to be done rather than a merge. It would also be nice to known if there are any triggers on the Customer table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)