need a sql quary

[mpoorrajab]

hi everyone ...

i need a mysql quary for my vb db that put the first post date of users instead of their registration date ...

some of our users have incorrect registration date because of i convert my forum from phpbb to vb and those users dont have reg date ...

thank you ...

[Lynne]

You used impex to import your users? You might want to ask over on vb.com how to fix this since I know I've seen this brought up before over there.

[mpoorrajab]

i asked them and they told me you cannot do anything ...

i think that if i put the first post date of each user instead of reg date , i can solve this problem so i want a query to do that ...

[mpoorrajab]

no answer yet?

[Lynne]

So, in the user table you want to enter the firstpostdate into the joindate field if the joindate field is empty (is it actually empty or does it have some other value in it)? It's not a simple 'copy' query because firstpostdate isn't another one of the fields in that table.

[mpoorrajab]

you mean is there is no way to do that?

there is some data in those registration date fields but they are wrong for example Jan-01-1970

[Lynne]

I'm sure there is a way to do it, but it's not a simple query which is probably why no one has come and posted it. And since, the field isn't empty, that makes it a bit more difficult because you can't just say WHERE joindate=''. You need to find something unique about the users that need their joindates changed. What is in the joindate field exactly - is it exactly the same for all the users you want to change? (It's gonna be in unixtime.) Also, what is a user has never posted - what do you put in that field then?

[Marco van Herwaarden]

Try the following query (create backup first!!!):
[sql]UPDATE user AS u
SET joindate = (SELECT MIN(p.dateline) FROM post AS p WHERE p.userid = u.userid)
WHERE u.joindate = 0;[/sql]

[mpoorrajab]

Originally Posted by Marco van Herwaarden

Try the following query (create backup first!!!):
[sql]UPDATE user AS u
SET joindate = (SELECT MIN(p.dateline) FROM post AS p WHERE p.userid = u.userid)
WHERE u.joindate = 0;[/sql]

can you please give this query to process on one user after i see changes do that for all users ...

[Marco van Herwaarden]

Change:

[sql]WHERE u.joindate = 0;[/sql]

to:

[sql]WHERE u.joindate = 0 AND userid = <<IdOfUser>>[/sql]


Replace <<IdOfUser>> with the userid.