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';
The error produced was:
ERROR 1064: You have an error in your SQL syntax near 'FROM table1 INNER
JOIN table2 ON table1.Company=table2.Company WHERE table1.Modi' at line 1
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 :>)
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...my coding knowledge is very weak and this was
obtained from a text:
#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, Modified
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");
/*mysql_query(&my_connection,
"Some UPDATE code" );*/
/*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;
field_count = 0;
while (field_count < mysql_field_count(&my_connection)) {
printf("%s ", sqlrow[field_count]);
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. In the section
/*mysql_query(&my_connection, "Some UPDATE code" );*/
I have been able to get a static update to work, such as "UPDATE table1 SET
Company = 'NewCompany' where Modified = 9", but I need it to input the
values which were obtained from the initial SELECT statement.
I've seen some incomplete code which defines a variable such as query[2048]
and then I assume somehow assigns a query to it which can then be used
similar to:
mysql_query(&my_connection, query);
Can somebody please help me get this code completed, as it is going nowhere
awfully fast at the moment.
Thankyou,
John Mravunac
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
