peter peter - 28 days ago 17
MySQL Question

How to update table with another table but no common field

I need to update the value in TableB, but the data in common field are not exactly the same. This is what i've tried so far

UPDATE TableB (`value`) SET (value * rate)

| TableA |
Rate t
2 2001
3 2002
4 2003

| TableB |
value t
1 2001-1
2 2001-2
3 2001-3
1 2002-1
2 2002-2
3 2002-3
1 2003-1
2 2003-2
3 2003-3


Result (Sorry I didnt check carefully for my data)

| TableB |
value t
2 2001-1
4 2001-2
6 2001-3
3 2002-1
6 2002-2
9 2002-3
4 2003-1
8 2003-2
12 2003-3

Answer

You can try This

TRIM(SUBSTRING_INDEX(TableB.t,'-',1))

Your query

UPDATE TableB JOIN TableA ON TableA.t =    
TRIM(SUBSTRING_INDEX(TableB.t,'-',1))SET TableB.value= TableB.value * TableA.rate  
Comments