Solved

mysql sql query

Posted on 2014-01-30
4
358 Views
Last Modified: 2014-02-13
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
Comment
Question by:magento
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39823407
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
 
LVL 5

Author Comment

by:magento
ID: 39823454
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
 
LVL 143

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).
0
 
LVL 5

Author Comment

by:magento
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.
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

631 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