3

I am trying to update Table B of a database looking like this:

Table A:
id, amount, date, b_id
1,200,6/31/2012,1
2,300,6/31/2012,1
3,400,6/29/2012,2
4,200,6/31/2012,1
5,200,6/31/2012,2
6,200,6/31/2012,1
7,200,6/31/2012,2
8,200,6/31/2012,2

Table B:
id, b_amount, b_date
1,0,0
2,0,0
3,0,0

Now with this query I get all the data I need in one select:

SELECT A.*,B.* FROM A LEFT JOIN B ON B.id=A.b_id WHERE A.b_id>0 GROUP BY B.id

id, amount, date, b_id, id, b_amount, b_date
1,200,6/31/2012,1,1,0,0
3,400,6/29/2012,1,1,0,0

Now, I just want to copy the selected column amount to b_amount and date to b_date 

b_amount=amount, b_date=date

resulting in

id, amount, date, b_id, id, b_amount, b_date
1,200,6/31/2012,1,1,200,6/31/2012
3,400,6/29/2012,1,1,400,6/29/2012

I've tried COALESCE() without success. Does someone experienced have a solution for this?

Solution:

Thanks to the answers below, I managed to come up with this. It is probably not the most efficient way but it is fine for a one time only update. This will insert for you the first corresponding entry of each group.

REPLACE INTO A SELECT id, amount, date FROM 
(SELECT  A.id, A.amount, B.id as Bid FROM A INNER JOIN B ON (B.id=A.B_id)
ORDER BY A.id DESC) 
GROUP BY Bid;
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Martin
  • 63
  • 4

2 Answers2

1

So what you are looking for seems to be a JOIN inside of an UPDATE query. In mySQL you would use

UPDATE B INNER JOIN A ON B.id=A.b_id SET B.amount=A.amount, B.date=A.date;

but this is not supported by sqlite as this probably related question points out. However, there is a workaround using REPLACE:

REPLACE INTO B 
SELECT B.id, A.amount, A.date FROM A 
LEFT JOIN B ON B.id=A.b_id 
WHERE A.b_id>0 GROUP BY B.id;

The query will simply fill in the values of table B for all columns which should keep their state and fill in the values of table A for the copied values. Make sure the order of the columns in the SELECT statement meet your column order of table B and all columns are mentioned or you will loose these field's data. This is probably dangerous for future changes on table B. So keep in mind to change the column order/presence of this query when changing table B.

Something a bit off topic, because you did not ask for that: A.b_id is obviously a foreign key to B.id. It seems you are using the value 0 for the foreign key to express that there is no corresponding entry in B. (Inferred from your SELECT with WHERE A.b_id>0.) You should consider using the null value for that. When you are using INNER JOIN then instead of LEFT JOIN you can drop the WHERE clause entirely. The DBS will then sort out all unsatisfied relations.

Community
  • 1
  • 1
Alexander
  • 1,068
  • 6
  • 22
  • 1
    Thank you, this works! One more thing, though: The Group By statement uses the last row corresponding to the query but I need the first. – Martin Jun 07 '12 at 16:48
0

WARNING Some RDBMS will return 2 rows as you show above. Others will return the Cartesian product of the rows i.e. A rows times B rows.

One tricky method is to generate SQL that is then executed

SELECT "update B set b.b_amount = ", a.amount, ", b.b_date = ", a.date, 
" where b.id = ", a.b_id
FROM A LEFT JOIN B ON B.id=A.b_id WHERE A.b_id>0 GROUP BY B.id

Now add the batch terminator and execute this SQL. The query result should look like this

 update B set b.b_amount = 200, b.b_date = 6/31/2012 where b.id = 1 

 update B set b.b_amount = 400, b.b_date = 6/29/2012 where b.id = 3

NOTE: Some RDBMS will handle dates differently. Some require quotes.

Andrew Kozak
  • 1,631
  • 2
  • 22
  • 35
kelley805
  • 7
  • Thank you! I get the columns accordingly but how do you execute appended columns? This does not seem to be the elegant way to go. – Martin Jun 05 '12 at 10:08