Hod do you update a field to the value of another field?

Databases
1

I want to update a field to the value of another field found in another row of the same table. I think this is possible, but i am not sure what the syntax would be. Each row has it’s unique key index.

I suppose my logic would be as follows:
UPDATE Categories SET isPage= (SELECT FROMCategories.isPage WHERE CatID=13) WHERE CatID=15

So its setting the value of isPage at row(CatID) 15, to whatever the value of isPage is at row(CatID) 13.

But, as I said before , the syntax is wrong however, and nearly an hour on google has proved fruitless. Any help would be greatly appreciated

2

you were on the right track, your subquery was just a bit messed up :slight_smile:

UPDATE Categories
   SET isPage = 
       (SELECT isPage 
          FROM Categories 
         WHERE CatID = 13 ) 
 WHERE CatID = 15
3

hmmmm… thanks, for the tip… but now I get this error:
#1093 - You can’t specify target table ‘Categories’ for update in FROM clause

4

bummer

try it like this –

UPDATE Categories AS this
     , Categories AS that
   SET this.isPage = that.isPage 
 WHERE that.CatID = 13  
   AND this.CatID = 15
5

IT WORKS… thank you…

mind if I ask… what doe sit mean??? ( I didnt know mySQL had objects??)

6

those aren’t objects… this and that are simply table aliases, required because we’re referencing the same table twice

i could’ve used any names for them…

UPDATE Categories AS humpty
     , Categories AS dumpty
   SET humpty.isPage = dumpty.isPage 
 WHERE dumpty.CatID = 13  
   AND humpty.CatID = 15
7

I think I got it… :slight_smile: … It’s clever the way it sets isPage to itself… in on case…

thanks again