| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-13-04, 22:32
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 5
|
|
|
Updating a table row, how?
|
|
Okay i have a table in my database that has a few rows. There called:
Userid
Threadid
date
You can obvioulsly tell what there for.
Anyway, I added a new row called 'threadtitle' but the values are all blank. Is there anyway that i can run a update query to add all the threadtitles to the proper threadid. All the thread ids have the correct threadid, but now i want to be able to use the thread title.
Basicly, i just want to update the row threadtitle with the correct thread titles based on the threadid row. TIA!
|
|
08-14-04, 02:57
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
1)
update tableA ta
INNER JOIN
tableB tb ON
ta.id = tb.id
Set ta.title = tb.title;
2)
update tableA ta
set ta.title = (Select tb.title from tableB tb where id = ta.id);
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
|
08-14-04, 10:52
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
|
|
"I added a new row called 'threadtitle' "
you mean a new column?
"All the thread ids have the correct threadid, but now i want to be able to use the thread title."
okay, makes sense
" just want to update the row threadtitle with the correct thread titles based on the threadid row"
unfortunately, this doesn't -- make sense, that is
could you please give a few sample rows of the table, before the update and what you want them to look like after?
|
|
08-14-04, 11:33
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
Quote:
|
Originally Posted by r937
could you please give a few sample rows of the table, before the update and what you want them to look like after?
|
Great analysis, and even better suggestion!
-PatP
|
|
08-14-04, 17:11
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 5
|
|
Sorry guys. Here we go:
--Hackinstall' (table)
----threadid (colum)
----dateinstalled (colum)
----userid (colum)
----threadtitle (colum)
threadtitle is new, and empty. I want to update that colum with the thread titles. Someone said this could work
PHP Code:
<?php
require('global.php');
$threads = $DB_site->query("SELECT threadid, title FROM " . TABLE_PREFIX . "thread");
while($thread = $DB_site->fetch_array($threads))
{
$DB_site->query("UPDATE YourTable SET title='$thread[title]' WHERE threadid='$thread[threadid]'");
}
echo "Script Done";
?>
Hope this helps.
|
|
08-14-04, 17:39
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Quote:
|
Originally Posted by LiveWire
threadtitle is new, and empty. I want to update that colum with the thread titles.
|
where are the thread titles supposed to come from?
|
|
08-14-04, 22:06
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
I have already provided the solution.
1)
update tableA ta
INNER JOIN
tableB tb ON
ta.id = tb.id
Set ta.title = tb.title;
2)
update tableA ta
set ta.title = (Select tb.title from tableB tb where id = ta.id);
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
|
08-15-04, 07:15
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
yes, robert, very nice syntax (which does not work in every database), but could you please make it relevant?
which one is tableB?
LiveWire has mentioned only one table so far
|
|
08-15-04, 07:40
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
Quote:
|
Originally Posted by r937
which one is tableB?
|
The table containing the source threadIDs.
1) is for SQL Server
2) is for Oracle.
Both 1) and 2) assume that each source threadID has only one threadTitle.
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
Last edited by r123456; 08-15-04 at 07:46.
|
08-15-04, 07:53
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
well, i'm sure the original poster will be quite satisfied with that
thanks for your efforts
|
|
08-15-04, 19:47
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 5
|
|
Your way didn't work, that script i posted above works perfect. Thanks for trying.
|
|
08-15-04, 19:52
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
whose way?
and thanks for telling us that you have solved the problem
your method doesn't scale, by the way
any time you do an sql operation inside a loop, your performance goes way down
no problem doing a one-shot update, but i wouldn't put something like that into production
|
|
08-15-04, 20:03
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
Quote:
|
Originally Posted by livewire
SELECT threadid, title FROM " . TABLE_PREFIX . "thread")
|
This would be tableB
Therefore, given the code you supplied:
Code:
UPDATE YourTable SET title='$thread[title]' WHERE threadid='$thread[threadid]'");
Substitute tableA with yourTable and tableB with tablePrefex.
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
Last edited by r123456; 08-15-04 at 20:07.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Updating a table row, how?
