The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 23, 2019, 01:51:49 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: mysql update wtf am i not seeing here?  (Read 2568 times)
Phaėton
Lifer
*****
Offline Offline

Posts: 555


⎝⏠⏝⏠⎠


View Profile
« on: March 05, 2010, 01:21:51 AM »

Code:
update wp_users.user_pass
   set wp_users.user_pass=user1.user_pass
  from wp_users, user1
 where wp_users.ID = user1.ID

Can another pair of eyes please look at this:

im getting:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from wp_users, user1
where wp_users.ID = user1.ID' at line 3
Logged

When I was your age we used to walk to the TV to change the channel....  _̴ı̴̴̡̡̡ ̡͌l̡̡̡ ̡͌l̡*̡̡ ̴̡ı̴̴̡ ̡̡͡|̲̲̲͡͡͡ ̲▫̲͡ ̲̲̲͡͡π̲̲͡͡ ̲̲͡▫̲̲͡͡ ̲|̡̡̡ ̡ ̴̡ı̴̡̡
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« Reply #1 on: March 05, 2010, 02:57:54 AM »

You don't need from .. you already say the table after update.
Logged

I met god and he had nothing to say to me.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: March 05, 2010, 09:04:58 AM »

Joined updates need to have both fields referenced in the update, even thought they are not both getting updates.
Also don't put the field name in the update assertion. It should simply be:

Simple update:
update wp_users set user_pass='value' where id='evalStr';


Multi-Table (This should do what you want, although I've had difficulty with this as well):
update wp_users w, user1 u set w.user_pass=u.user_pass where u.id = w.id;


Looks to me like you're trying to move all the user1 user_pass into the wp_users user_pass. As an exercise, we can do it in code as well.

In PHP you'd simply need two connections to the database, or two processes:

$db1 = new dbConnection($hostA$userA$passA$databaseA);
$db2 = new dbConnection($hostB$userB$passB$databaseB);

$db1->query("select id, user_pass from $db2");
while (
$db1->fetchAssoc())
	
$db2->query("update wp_users set user_pass='{$db1->row['user_pass']}' where id={$db1->row['id']}");

Alternately, done with a single connection:
$db1 = new dbConnection($hostA$userA$passA$databaseA);
$db1->query("select id, user_pass from $db2");
while(
$db1->fetchRow())
	
$todo[$db1->row[1]] = $db->row[0];

$db1 = new dbConnection($hostB$userB$passB$databaseB);
foreach(
$todo as $id=>$pass)
	
$db2->query("update wp_users set user_pass='$pass' where id=$id");

PHP is very slow by comparison, because every row must be brought back to the machine for evaluation. In this case, if the joined update didn't work, I'd whip up a quick stored procedure.


PROCEDURE movePW()
BEGIN
   declare thisID, thisPass varchar(64);
   declare keepGoing tinyint;
   declare original cursor for select id, user_pass from user1;
   declare continue handler for not found set keepGoing = 0;

   set keepGoing = 1;
   open original;
   fetch original into thisID, thisPass;
   while keepGoing = 1 do
      update wp_users set user_pass=thisPass where id=thisID;
      fetch original into thisID, thisPass;
      end if;

      fetch items into thisID;
   end while;

END


With all that code, I hope you can get the simple multi-table update to work... Wink
« Last Edit: March 05, 2010, 09:45:56 AM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« Reply #3 on: March 05, 2010, 09:20:21 AM »

Joined updates need to have both fields referenced in the update, even thought they are not both getting updates.
To clearify, if I do join update, I need to have FROM also? I have never done join update so that's why I'm asking Smiley
Logged

I met god and he had nothing to say to me.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: March 05, 2010, 09:45:32 AM »

No, you *don't* use from ... the update is where both tables are referenced, as I did in my example above.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!