We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

phillystyle123
on
Medium Priority
392 Views
Last Modified: 2012-05-06
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'
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
Can you show us the data base schema, please?
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.

Author

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
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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)
Top Expert 2008

Commented:
>> 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?
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?
Most Valuable Expert 2011
Author of the Year 2014

Commented:
@cxr: Maybe the effort is to UPDATE instead of INSERT -- kind of hard to guess, eh!?

;-)

Ray
that's why I think they first mean to

ALTER Table1 ADD COLUMN LField text;

Author

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'
Most Valuable Expert 2011
Author of the Year 2014
Commented:
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?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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

Top Expert 2008
Commented:
Try this:

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

Open in new window

Author

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`
Top Expert 2008

Commented:
You must have it in the table reference at the start of the statement:

UPDATE Table2,Table1 ...
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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!

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.