You are quite right, you can't currently do sub-selects in MySQL, though
your example doesn't actually use
a sub-select, it has no SELECT clause appearing in the WHERE clause.

For reference, there are a couple of 'standard' workarounds to the lack of
sub-selects.

1) Rewrite the query to avoid the subselect,
http://www.mysql.com/doc/M/i/Missing_Sub-selects.html has some examples.

If that is not possible, then

2) first execute the sub-select into a temporary table, and then replace the
sub-select clause of your original table using the temporary table.

CREATE TEMPORARY TABLE foo SELECT col1, col2 FROM existing table WHERE....
is the extra bit of syntax you need to create temporary tables, then join
the temporary
table to the main part of your query where you would have used a sub-select.


The second part of your question
> ID field, but if I want to populate more than one field,
I don't quite understand, since your original SQL already does it, it
simply involves listing more than column, each separated by a comma.

UPDATE table1 SET col1 = "me",  col2="you" WHERE col1="somevalue";

In embedded code all you do is use the same syntax.


I'm wondering if your confusion is thinking that you need to execute an
UPDATE statement once on each row you want to change?
There is no problem combining the UPDATE and SELECT statements, so
the update executes on multiple rows, with the data depending on the row
being updated.
The syntax takes the form:

UPDATE table1, table2 SET table1.col2 = table2.coly WHERE table1.col1 
table2.colx;

In this update the values of table1.col2 are update to make them the same as
the value returned
in table2.coly when joining table1.col1 with table2.colx.

In general writing a procedural loop to wrap SQL statements is usually
unnecessary,
you just need to find the right SQL to allow the database to perform the
looping for you.

Hope this helps, though I'm not sure I properly understood your original
question.

Rick


-----Original Message-----
From: John Mravunac [mailto:johnmravunac@c...]
Sent: 02 October 2001 04:28
To: Pro Linux Programming
Subject: [pro_linux_programming] HELP PLEASE - C API code help: UPDATE
query using result from SELECT


Hi,

I believe that the query below is correct, but I do not believe that it is
possible in MySQL currently due to it not supporting sub-selects:

UPDATE table1 SET ID=table2.ID, Company=table2.Company,
Modified=table2.Modified FROM table1 INNER JOIN table2 ON
table1.Company=table2.Company WHERE table1.Modified='9' AND
table1.Company=table2.Company;

If you can't tell from the SQL query above...what I am trying to do is
update data in table1 with data from table2...the two tables have pretty
much identical information, although table2 has updated data and table1
can't just be overwritten because it may have something newer than table2
again (this is only part of the entire process which involves two MySQL
servers and a MS SQL server :>)

Example data:

table1
ID                 Company               Modified
NULL             ABC                      9
101               DEF                       0

table2
ID                 Company               Modified
200               ABC                      0

So here I would like to update table1's record for ABC with table2's ID and
Modified data, so after the code is run I would end up with table1 looking
like:

ID                 Company               Modified
200               ABC                      0
101               DEF                      0

table1 & table2 description:
ID              int(11)
Company    varchar(20)
Modified     int(11)

I've read that the only way to do it at the moment is using code such as C
or C++. Does anyone have any experience with this type of procedure?

My code so far is below:

#include <stdlib.h>
#include <stdio.h>

#include "/usr/local/mysql/include/mysql/mysql.h"

MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;

void display_row();

int main(int argc, char *argv[]) {
         int res;
         uint i = 0;

         query = 0;

         mysql_init(&my_connection);
         if (mysql_real_connect(&my_connection, "localhost", "username",
"password", "database", 0, NULL, 0)) {

                 printf("Connection success\n");
                 res = mysql_query(&my_connection, "SELECT ID FROM table1
WHERE Modified = 9");

                 if (res) {

                         printf("SELECT error: %s\n",
mysql_error(&my_connection));

                 } else {

                         res_ptr = mysql_store_result(&my_connection);
                         if (res_ptr) {
                                 while ((sqlrow = mysql_fetch_row(res_ptr)))
{
                                         for (i=0; i <
mysql_num_fields(res_ptr); i++)
                                         printf("%s\n",sqlrow[i]);
                                         printf("Fetched data...\n");
                                         display_row();
                                 }
                                 if (mysql_errno(&my_connection)) {
                                         fprintf(stderr, "Retrieve error:
%s\n", mysql_error(&my_connection));
                                 }
                         }
                         mysql_free_result(res_ptr);
                 }
                 mysql_close(&my_connection);

                 } else {
                         fprintf(stderr, "Connection failed\n");
                         if (mysql_errno(&my_connection)) {
                                 fprintf(stderr, "Connection error %d:
%s\n", mysql_errno(&my_connection), mysql_error(&my_connection));
                         }
                 }

                 return EXIT_SUCCESS;
         }

void display_row() {
         unsigned int field_count;
         char query[2048];

         field_count = 0;
         while (field_count < mysql_field_count(&my_connection)) {
                 printf("%s ", sqlrow[field_count]);
                 sprintf(query, "UPDATE table1 SET ID = '%s', WHERE
table1.Modified=9, sqlrow[field_count]);
                 mysql_query(&my_connection, query);
                 field_count++;
         }
         printf("\n");
}

This code works very well in obtaining the data from the tables, but I need
to get a procedure that will perform the UPDATE once the necessary data has
been obtained.

This code currently will run an update, sticking the value of %s into the
ID field, but if I want to populate more than one field, I'm not sure I
would go about this?? Also, what I have so far will not update only the
fields with identical Company names in both tables, can anybody give me
some pointers here?

Would anybody perhaps suggest that I use ctrcat rather than sprintf to
build up the query?

Thankyou,
John Mravunac