Solved

mysql sql query

Posted on 2014-01-30
4
342 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
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

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

Author Comment

by:magento
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 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

15 Experts available now in Live!

Get 1:1 Help Now