0

I have a mysql table named MEMBER with a date column called 'joined'. Unfortunately the initial code for my application did not update this column properly so the first couple of thousand members have a null joined value.

I have successfully written a query (below) that finds the first date a member made a transaction from table TRANSACTIONSUMMARY and safely assumes this is the date they probably joined. 

SELECT `member_id`,`member_name`, `created` 
from transactionsummary
group by `member_id`

The problem I am having is how to update MEMBER.joined with the TRANSACTIONSUMMARY.created value for every member that has a null joined value. I can manage to update 1 member at a time with a WHERE member.id = "1" for eg but is it possible to do all in one go?

ChrisF
  • 134,786
  • 31
  • 255
  • 325
southafricanrob
  • 331
  • 3
  • 15

1 Answers1

0

You can write update with join, as described there: How to do 3 table JOIN in UPDATE query?

But I am not sure how group by will work with that. If not, you will need to put the result from transactionsummary to temporary table and then join memeber with temp table.

Community
  • 1
  • 1
jpesout
  • 688
  • 5
  • 12