mysql sql query

Posted on 2014-01-30
Last Modified: 2014-02-13

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


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


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


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

Table name: Topic
3000    sometime           sometime
4000    sometime           sometime


3000      1390284754        1390284754    
4000       1390284800        1390284800

Question by:magento
  • 2
  • 2
LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 39823407
would this article help you to build the sql needed?

I would wonder if you really WANT to update the date values, or rather "fix" the query for the display "order" ?

Author Comment

ID: 39823454
Hi Angelll,

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

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);

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

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

LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39823482
i think that should do it instead, all fine as far as I can see (without testing).

Author Comment

ID: 39857932
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.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now