typical UPDATE WHERE SELECT problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pixienick
Forum Newbie
Posts: 12
Joined: Sun May 28, 2006 6:58 pm

typical UPDATE WHERE SELECT problem

Post by pixienick »

Can somebody straighten out my sql? I know what i want i just dont quite know how to get it. I want to empty all the fields in 'one table 'details' but have the id from another table 'advert' which has a field for the unique number in details. Do i need EXIST or something? This is what i got so far;

Code: Select all

UPDATE details SET SortCode='', regNum='', Make='' WHERE (SELECT id FROM advert WHERE details.id=advert.detailsID AND ( advert.id=362 OR advert.id=54 ))
Any help much appreciated.
User avatar
HCBen
Forum Commoner
Posts: 33
Joined: Thu Jun 22, 2006 3:15 pm
Location: Indiana

Post by HCBen »

You have to join the details table in your sub-select.

Try this:

UPDATE details SET SortCode='', regNum='', Make='' WHERE (SELECT advert.id FROM advert INNER JOIN details ON details.id = advert.id AND ( advert.id=362 OR advert.id=54 ))

Cheers
Ben
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Don't you want to set your WHERE clause to actually evaluate against something? I'm no DB master, but I thought that you would have to do something along the lines of...

Code: Select all

UPDATE details SET SortCode='', regNum='', Make='' WHERE id IN (SELECT advert.id FROM advert INNER JOIN details ON details.id = advert.id AND ( advert.id=362 OR advert.id=54 ))
Of course, I am just throwing my two cents in here. I could be totally off. In which case, disregard this post.

EDIT | Wrapped in code seeing as the syntax highlighter is broken right now.
User avatar
HCBen
Forum Commoner
Posts: 33
Joined: Thu Jun 22, 2006 3:15 pm
Location: Indiana

Post by HCBen »

Everah, you're absolutely right. Oversite on my part in failing to mention that in my post. :oops:
pixienick
Forum Newbie
Posts: 12
Joined: Sun May 28, 2006 6:58 pm

Post by pixienick »

Hiya

thanks for your help so far, but I'm still having a bit of difficulty, I havent manged to make a valid sql statement yet with this! My code is a little more confusing than i previously wrote.

Code: Select all

UPDATE details SET SortCode='', regNum='', Make='' WHERE DetID IN (SELECT advert.detailsID FROM advert INNER JOIN details ON details.DetID=advert.detailsID AND ( advert.id=362 OR advert.id=0 ))
You see the two tables are not shared via the 'id' field 'advert' has an 'id' field and that is the number i am searching by. 'details' has 'DetID' as its unique id. Each 'advert' also has a 'detailsID' field which corresponds to the 'DetID'of a single record in 'details'.

Do you think my naming practice is silly? Would it have been better to have them linked via their unique identifier with the same name ('id'), like in your examples? ? I've come a bit too far with this method now though. Am I right in thinking the field selected by the nested SELECT (in this case advert.detailsID) is what should correspond to the field before IN ('DetID')?
And does the nested SELECT not need a WHERE at the end?

Thanks again
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Are you using this with PHP or are you doing this at the database level?
pixienick
Forum Newbie
Posts: 12
Joined: Sun May 28, 2006 6:58 pm

Post by pixienick »

Hi there.
It am implementing it in PHP, but whilst i'm designing i just put the sql the php generates to the screen, so i can copy and paste it into phpmyadmin untill i get it working.
The example i'm showing is generated by a script. At the end where i check for 'advert.id's could go into the hundreds of ORs, is that alright?
Cheers
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

The reason I ask if you are using PHP is that the last time I was faced with this type of thing, I ran two queries to do the job. One query was the subquery which I read into a comma separated string. I then used that comma separated string as the IN() parameter of the second query. This is very similar to what you are doing here.
pixienick
Forum Newbie
Posts: 12
Joined: Sun May 28, 2006 6:58 pm

Post by pixienick »

I see what you mean. So i could use 1 SELECT to get me a list of ids then 1 UPDATE to update a number of records in the other table. Yeah I guess i'll do that. It's just not as concise is it? To get the list of ids will require multiple fetch_array()s wont it? It would be nice to know how to do it in in the one sql line, what I'm doing must not be that uncommon. I've tried it every which way with that IN command but to no avail!
Thanks for all the help.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Actually, I should probably rephrase my last post. I only did it that way because MySQL 3.23 did not support subqueries. What you want to do should be easy enough with a version of MySQL 4.1+. I know it can be done in one query. The exact syntax to use, that I am a little unsure of.
pixienick
Forum Newbie
Posts: 12
Joined: Sun May 28, 2006 6:58 pm

Post by pixienick »

Yeah well i'm sure it can be done but the exact syntax has had stumped for 2 days! I did it the other way in the end. It still only required two sql queries, but i'm looping through a whole lot of mysql_fetch_arrays. Is fetch array the equivalent of an sql query in overhead, because it is still consulting the database every time is it not?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You should only use mysql_fetch_array twice: once in the first query, once in the second.

Code: Select all

<?php
$sql = "SELECT id FROM table"; // Or add in some WHERE clause
if (!$result = mysql_query($sql))
{
    die('Could not grab the id numbers from the table: ' . mysql_error());
}

$comp_string = '';
$comp_string_row = array();
$comp_string_row[] = mysql_fetch_array($result);

$check_count = count($comp_string_row);
if ( $check_count > 0)
{
    for ($i = 0; $i < $check_count; $i++)
    {
        $delim = ( $i == $check_count - 1) ? '' : ', ';
        $comp_string = $comp_string_row[$i]['id'] . $delim;
    }
}

// At this point $omp_string should look like (1, 23, 34, 45, 56) etc

// Now do your second query using $comp_string within the IN()
?>
pixienick
Forum Newbie
Posts: 12
Joined: Sun May 28, 2006 6:58 pm

Post by pixienick »

Oh wow, that is good, didnt know you could do that like that.
Nice one
thanks again
Post Reply