0

I found similar ones at here, but not exactly the same.

Here is what I need:

I have two tables a and b, with the following structures:

Table a:

id  b_id    b_col2 b_col3 col5
1   NULL    NULL   NULL   NULL
2   XXX     XXX    XXX    XXX

Table b:

id    col2 col3
101   ABC  DEF
102   XXX  XXX

UPDATE: The goal is that with three given inputs (id, b_id, col5), one row of table a should be updated accordingly. Specifically, b_col2 and b_col3 are extracted from table b with that b.id = b_id. For example, I want to update the first item of a with b_id=101 and col5 = ZZZ. Notice that b_col2 = ABC and b_col3 = DEF in this case, the updated row should be:

id  b_id    b_col2 b_col3 col5
1   101     ABC    DEF    ZZZ

I know there is at least a not-so-effective way which I don't like: 

update a set 
    a.b_id = some-id, 
    a.b_col2 = (SELECT col2 FROM b WHERE b.id = some-id),
    a.b_col3 = (SELECT col3 FROM b WHERE b.id = some-id),
    ...
    where a.`id` = xxx

As I said, this is not a very good method, and I am just wondering if there is any better method?

Community
  • 1
  • 1
Dainy
  • 89
  • 9
  • There's no way to do this, unless you have some shared identifier between the two tables. Otherwise, MySQL has no way of knowing which rows in A go with which rows in B. – Steven Moseley Aug 31 '15 at 20:52
  • Is `b_id` always offset by 100 from the equivalent `a_id` record? – Zwander Aug 31 '15 at 21:11
  • Then how do YOU know they are related? Is it by the order they are entered? – Zwander Aug 31 '15 at 21:13
  • Your question implies that you want to automatically populate the database with equivalent rows. Could you please update your question with the full context so that we can understand and assist you. :) – Zwander Aug 31 '15 at 21:17
  • 1
    @Zwander have updated the question. Hope it's clearer now :) – Dainy Aug 31 '15 at 21:24

0 Answers0