(My)SQL Iteratively using data from one table to fill another /select from lists

Question

I have two (My)SQL tables A, B. A has many columns A.(a,b,c,d...). B contains only two columns x,y. The number of rows in B is (much) less than that in A. All values in B.x are unique.

A is fully populated. In B only the values in B.x exist, B.y is empty and I want to fill B.y with the values from (say) A.b, corresponding to each value of B.x which appear as A.a

In other words, what I need to do is for each B.x find the match in A.a and take the corresponding A.b and insert it into B.y in the row containing the value of B.x. I then need to iterate this over all the values in B.x.

For reasons that we don't need to go into I need to do this in a single SQL statement; no PHP, no Python, etc. No embedding. What I am looking for is a way of making an iterated selection from one into the other. I also want to avoid the creation of temporary tables.

FWIW I can't make any assumptions about the number of rows in each table.

Hope that this is clear enough. Thanks for any help that you are able to give.

Ilion · Accepted Answer · 2012-01-22 22:41:20Z

1

This assumes A.a values are also unique. If they are not you may need to look at a way of grouping them.

update B inner join A on B.x=A.a set B.y=A.b;

answered Jan 22, 2012 at 22:41

Ilion

6,8923 gold badges28 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PeterDz Over a year ago

This works!...needed a bit of tinkering but that's my DB and nothing that I put in the question, certainly nothing that you could have known. Thanks :-)

Amir Pashazadeh · Accepted Answer · 2012-01-22 22:40:38Z

0

I believe you are going to normalize your A table, if for each A.a value the value of A.b is unique the following query shall work

update B
   set B.y =
       (select distinct A.b
          from A
         where A.a = B.x)

answered Jan 22, 2012 at 22:40

Amir Pashazadeh

7,3124 gold badges44 silver badges71 bronze badges

1 Comment

PeterDz Over a year ago

Returned Error Code 1242 - though I thought that it would work :-(

Collectives™ on Stack Overflow

(My)SQL Iteratively using data from one table to fill another /select from lists

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related