SQL - replace values in column based on values from other table

Hi all,

I use SQL so little that I'm starting from scratch on it every time I have to use it...

My problem is that I have a few hundred rows in a column called A_ID in a table called FR like this:
A_ID
1249
5782
3842
etc..

Open in new window


Another table called A_BLY is like this:

A_BIL  |  A_ORIG
5782   |  1589
3842   |  1651
1249   |  1732

Open in new window


I need to replace the values in FR.A_ID with the matching values in A_BLY, so that table FR looks like this in the end:

A_ID
1732
1589
1651
etc..

Open in new window


I hope this is clear enough!

Any help would this would be greatly appreciated, thanks.

Paul
paulc2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
Can you try this? Please backup your table before running update statement.

update F
Set F.A_ID=A.A_BIL
From FR F
join A_BLY A
on F.A_ID=A.A_ORIG
0
paulc2000Author Commented:
Thanks.  It returned a syntax error.  I know MySQL is funny about backticks, would you mind including them in the right places please?
0
ste5anSenior DeveloperCommented:
This should work:

UPDATE FR, A_BLY
SET FR.A_ID = A_BLY.A_ORIG
WHERE FR_A_ID = A_BLY.A_ID;

Open in new window

See also 13.2.10. UPDATE Syntax.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paulc2000Author Commented:
Great, thank you.  It worked with some backticks added in.

One question - in the first line, is it necessary to include A_BLY in the UPDATE?

UPDATE FR, A_BLY

Open in new window

0
ste5anSenior DeveloperCommented:
Basically, yes. It's the old pre SQL-92 syntax without the JOIN keyword. Depending on your version of MySQL you may use the JOIN clause. See 13.2.8.2. JOIN Syntax. E.g.

UPDATE FR
SET FR.A_ID = A_BLY.A_ORIG
JOIN A_BLY ON FR_A_ID = A_BLY.A_ID;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.