I am struggling with this myself right now. I posted a similar question to
this group earlier in the day and it must be a poser because no one has
responded.
I fear that the only way to do this with a single sql statement is using the
replace command:
Let's say you have 2 tables like this:
MainTbl
id int not null (primary key),
fld1 int,
fld2 int,
fld3 int
UpdTbl
id int not null (primary key),
fld2 int
And let's say that you have 20,000 recs in MainTbl and only 100 recs in
UpdTbl.
You want to join the 2 tables and flag the value from UpdTbl.fld2 into
MainTbl.fld2, right?
The best approach I have thought of is
REPLACE INTO MainTbl
SELECT MainTbl.id, MainTbl.fld1, UpdTbl.fld2, MainTbl.fld3
FROM MainTbl INNER JOIN UpdTbl ON MainTbl.id = UpdTbl.id;
I have not tried this yet but if I read the manual correctly, it should
work.
If anyone has a better solution, please fill us in.
Additionally, I would like ideas on how to work around MySQL's inability to
handle deletes like the following:
DELETE MainTbl
FROM MainTbl INNER JOIN DelTbl ON MainTbl.id = DelTbl.id;
> -----Original Message-----
> From: John Mravnuac [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 19, 2001 8:28 PM
> To: [EMAIL PROTECTED]
> Subject: 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';
>
> 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
>
>
---------------------------------------------------------------------
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
