[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

update from 1 table to another - mysql - need to use WHERE clause

hi -

i need to copy data from 1 table to another where the IDs equal each other - this is what i have thus far:

INSERT INTO Table1 (EField) (SELECT LField FROM Table2 WHERE Table1.ID=Table2.ID)

I'm getting

#1054 - Unknown column 'Table1.ID' in 'where clause'
0
phillystyle123
Asked:
phillystyle123
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
Ray PaseurCommented:
Can you show us the data base schema, please?
0
 
Michael701Commented:
describe Table1
describe Table2

and post results.

in general you're lost because you're tring to INSERT new records on a match of existing records.
0
 
phillystyle123Author Commented:
I've pulled this off without the WHERE CLAUSE - there's gotta be a way to do it with IT.


Table1
Field       Type       Null       Default       Comments
ID       int(11)       No                
STATE_ID       int(11)       Yes        0         
CField       text       Yes        NULL         
EField       text       Yes        NULL         


Table2
Field       Type       Null       Default       Comments
ID       int(11)       No                
STATE_ID       int(11)       Yes        0         
LField       text       Yes        NULL
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ray PaseurCommented:
Not exactly sure of the syntax, but maybe something like this...

INSERT INTO Table1 (EField) VALUES (SELECT ID, LField FROM Table1, Table2  WHERE Table1.ID=Table2.ID)
0
 
Roger BaklundCommented:
>> where the IDs equal each other

I don't understand this. You are inserting NEW rows in Table1, they don't yet have any ID. Do you want to insert duplicate ID's in Table1? Or maybe you what you want is an update of existing rows in Table1?
0
 
Michael701Commented:
Ok, few question

First do you mean to be matching STATE_ID.Table1=STATE_ID.Table2

Next field names you have CField and EField in table 1 and LField in table 2

Do you want to ADD a new column in table 1 called LField and then copy the data from table 2 into this new field in table 1?
0
 
Ray PaseurCommented:
@cxr: Maybe the effort is to UPDATE instead of INSERT -- kind of hard to guess, eh!?

;-)

Ray
0
 
Michael701Commented:
that's why I think they first mean to

ALTER Table1 ADD COLUMN LField text;

0
 
phillystyle123Author Commented:
Yes -sorry -that is what i'm trying to do - update.

I need to copy data from one field in Table1 over to a field of a different name in Table2:

UPDATE `Table2` SET  `Table2`.`Efield`=`Table1`.`Lfield` WHERE `Table2`.`STATE_ID`=`Table1`.`STATE_ID`

When I run this in phpMyAdmin I get

#1054 - Unknown column 'Table1.STATE_ID' in 'where clause'
0
 
Ray PaseurCommented:
Yes, that is because there is no reference to Table1, except in the WHERE clause.

Why not just write a quick PHP script to iterate?
0
 
Michael701Commented:
Something like this?
$sql_command = "select * from Table2";
$rsTable2 = mysql_query($sql_command);
while ($row=mysql_fetch_assoc($rsTable2))
{
  $sql_command = "update Table1 set EField='".$row['LFeild']."' where STATE_ID='".$row['STATE_ID']."'");
  $ok=mysql_query($sql_command);
}

Open in new window

0
 
Roger BaklundCommented:
Try this:

UPDATE `Table2`,`Table1` SET  `Table2`.`Efield`=`Table1`.`Lfield` WHERE `Table2`.`STATE_ID`=`Table1`.`STATE_ID`

Open in new window

0
 
phillystyle123Author Commented:
UPDATE `Table2` SET  `Table2`.`Efield`=`Table1`.`Lfield` WHERE `Table2`.`STATE_ID`=`Table1`.`STATE_ID`

I'm referring to it here:

SET  `Table2`.`Efield`=`Table1`.`Lfield`

and here:

WHERE `Table2`.`STATE_ID`=`Table1`.`STATE_ID`
0
 
Roger BaklundCommented:
You must have it in the table reference at the start of the statement:

UPDATE Table2,Table1 ...
0
 
Ray PaseurCommented:
You'll get this sorted out eventually, but while you are waiting for EE suggestions, spend a few bucks on the very good book, "MySQL, the Complete Reference" by Vikram Vaswani.  Get whatever is the latest version.  It's on my desk all the time!
0
 
phillystyle123Author Commented:
Thanks Everyone!  Didn't seem to make sense that I had to reference the table I wasn't updating as well as the one I was - much appreciated.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now