asked on

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

Sharath S

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

paulc2000

ASKER

Thanks. It returned a syntax error. I know MySQL is funny about backticks, would you mind including them in the right places please?

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

paulc2000

ASKER

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

ste5an

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