• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

mysql sql query

Hi,

Getting trouble with my forum migration  due to time value issue ,the message in forum showing in incorrect order.

So need two sql to fix them .

The parent time of a thread should be updated to all the child in that thread

My database is MYSQL


Query1:
Input

Table name: Message
parent , thread , time
0           3000         1390284754
1           3000         1390284800
2           3000         1390284820
3           3000         1390285900
0           4000         1390284800
1           4000          1390284950

Output

parent , thread , time
0           3000         1390284754
1           3000         1390284754
2           3000         1390284754
3           3000         1390284754
0           4000         1390284800
1           4000          1390284800

Query2:

There is a another table. So that need to be updated with the value of time to first_post_time and last_post_time

input:
Table name: Topic
thread,first_post_time,last_post_time
3000    sometime           sometime
4000    sometime           sometime


Output:


thread,first_post_time,last_post_time
3000      1390284754        1390284754    
4000       1390284800        1390284800


Thanks
0
magento
Asked:
magento
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
would this article help you to build the sql needed?
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

I would wonder if you really WANT to update the date values, or rather "fix" the query for the display "order" ?
0
 
magentoAuthor Commented:
Hi Angelll,

This sql working fine, do you mind advice any changes needed or any other better way of doing it.

Thanks
create table message 

(
parent int(10),
thread int(10),
time int(10)
)

insert into message values (0,3000,1390284754);
insert into message values (1,3000,1390284800);
insert into message values (2,3000,1390284820);
insert into message values (3,3000,1390285900);
insert into message values (0,4000,1390284800);
insert into message values (1,4000,1390284950);


create table topic
(
thread int(10),
first_post_time int(10),
last_post_time int(10)
)
insert into topic values (3000,1390284823,1390284887);
insert into topic values (4000,2390284523,2390284897);


Query1:
update  `message` a , (SELECT *
FROM `message`
WHERE parent =0
GROUP BY thread)b set a.time = b.time WHERE a.thread=b.thread

Query2:
update topic a , message b set a.first_post_time=b.time ,a.last_post_time=b.time
where a.thread=b.thread and b.parent=0

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i think that should do it instead, all fine as far as I can see (without testing).
0
 
magentoAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for angelIII's comment #a39823407
Assisted answer: 0 points for magento's comment #a39823454

for the following reason:

With the help of Angel , i have figured the code.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now