Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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