Update multiple rows using multiple select criteria

[ginerjm]

As the title indicates, a tricky query problem.

 

I have a set of query results that contain two unique criteria that I would like to use in a second query to update a third field.

 

For ex.

 

My select query yields among other things a FldA and FldB on each record.  These are not unique themselves, but as a combination they will select a unique record (Key!!).  I would like to take this set of fields and perform an update on a single table where the records to be updated (updating  'FldC') match the FldA and FldB pairs. 

 

I have seen many examples but none seem to fit my case, or else I don't understand this fully. 

 

Thanks in advance!

[Barand]

Perhaps a little code and table structures would give us more information about you are trying to do.

 

Is fieldC in different table from the first query, for instance?

[ginerjm]

All three fields are in the same table.  My update will only affect one table.  The question is - how do I make my update query only update those having the following pseudo-where clause:

 

"update my_table set FldC='x' where (FldA='vala1' and FldB='valb1') OR (FldA='vala2' and FldB='valb2')  OR (FldA='vala3' and FldB='valb3') OR (FldA='vala4' and FldB='valb4') ....

 

where my values for FldA and FldB come from the results of a previously generated select query.  I could actually create the above query using php but the 'where' clause could be posting 200 records and my thought is that would be one hellaciously long query statement for the server.

[ginerjm]

Let me add some more info to my problem.  My data is involved with the game of handball and is the match data for a tournament.  FldA represents the event code for the match of which there are many.  FldB represents a unique match id for an event code.  There can be many similar match ids but NOT within an event.

 

For ex., FldA can be MB, MC or MO.  FldB can be R1S0108, R1S0207, R1S0304, etc.  These values can exist in every single event, BUT for a specific event only once.

 

My select query has pulled a set of matches (records) that match some criteria and after I have finished using that data I want to go back and post something in one of my tables for every record that was selected and processed.  So I want to run an Update query without having to re-generate this huge query again (there are 8 tables in it and there can be upwards of 200 rows in the result).  I could re-write the query to only cull out the bare minimum (but essential) data which would use only one table, but I still don't know how to get my update to affect only each of the records in that select query

 

Something like this:

 

update match_table  a set printed ="Y"

   join (select eventcode,matchid from match_table where eventcode='MO' ) b

  on a.eventcode = b.eventcode and a.matchid = b.matchid

 

is what I'm thinking.  Can anyone confirm that this would work?

[Barand]

Do the records from you original select query have unique record ids?

 

If so, you could store those ids in an array and use the array to define the rows to be updated

Edited November 6, 2013 by Barand

[ginerjm]

No they don't.  The FldA and FldB that I mention are unique as a combination though.

[Barand]

Easiest way, probably, is to concatenate the two fields and store those in an array.

 

Then, for example

$keys = array ('FldA1FldB1', 'FldA2FldB2', 'FldA3FldB3'); // stored concatenated values

$keylist = join("','", $keys);
$sql = "UPDATE tablename SET FldC = 'x'
        WHERE CONCAT(FldA, FldB) IN ('$keylist') ";

[ginerjm]

This is what I'm trying now.

 

 

<?
 $q = "update tms_test_matchdata p set p.Printed='Y'
   join (select m.* from tms_test_matchdata m
     where m.Tourn_key='$tourn_key' and m.Printed is null) s
   on p.Tourn_key=s.Tourn_key and p.Eventcode=s.Eventcode and p.MatchId=s.MatchId";
 
?>

 

The error I get is

 

check the manual that corresponds to your MySQL server version for the right syntax to use near

 

'join (select m.* from tms_test_matchdata m where m.Tourn_key='TMStest' and ' at line 2
 

[ginerjm]

Re: your last

As I said earlier I don't know that a huge IN() list would be acceptable.  It could have 200+ entries in it.

[Barand]

try

 $q = "update tms_test_matchdata p
        join (select m.* from tms_test_matchdata m
        where m.Tourn_key='$tourn_key' and m.Printed is null) s
        on p.Tourn_key=s.Tourn_key and p.Eventcode=s.Eventcode and p.MatchId=s.MatchId
        set p.Printed='Y'";

[ginerjm]

Well, I'll be!  That worked.  Odd - that putting the set at the end made all the difference.

[ginerjm]

For those reading along here is my final query:

 

 

  $q = "update tms_test_matchdata p
   join (select m.* from tms_test_matchdata m
   where m.Tourn_key='$tourn_key' and m.Printed IS NULL) s
   on p.Tourn_key=s.Tourn_key and p.Eventcode=s.Eventcode and p.MatchId=s.MatchId
   set p.Printed='Y'";
 

 

Note the placement of the set clause.

[Barand]

Well, I'll be!  That worked.  Odd - that putting the set at the end made all the difference.

Not at all

 

With a single file it would be "UPDATE filename SET a = b". The filename in this case is a JOIN expression