Move data within same table 3

[jude99]

Is there a way to move data from field1, field2, field3, field4, id IN(200, 201, 202, 203, 204) in table1

to field1, field2, field3, field4, id IN(800, 801, 802, 803, 804) in table1

Ideally the data would be moved rather than duplicated.

Is this possible, please, Experts?

 

[r937]

could you repeat the question please?

what does IN(...) mean?

r937.com | rudy.ca

 

[jude99]

Am I using the IN operator in the wrong place? Apologies if so. I want to move the data of a few columns in the same table to the same columns but in another part of the table.

And I thought I could the IN operator (with the ID numbers) to specify which of the rows the data is in, and use the IN operator to say where I want them to go.

It would be better if they could be moved rather than duplicated. I'm sure there must be a way to do it, but I suspect that only experienced users like yourself know the magic way.

 

[r937]

okay, here's the "move" --

UPDATE table1
SET id = id + 600
WHERE id IN (200, 201, 202, 203, 204)





r937.com | rudy.ca

 

[jude99]

Easy to make fun of people who want to learn and haven't quite grasped it. I probably use the wrong terms at times, and I apologise for that.

I used the word 'move' because I thought it would show you best what I am trying to do.

And I am grateful for your sticking with this. But I can't see how SET id = id + 600 comes into it.

I want to take, say, address1, address2,address3,address4, from several specific rows which I would identify by their IDs which are unique. And I want to update, insert, put, that data into rows which I would specify by giving their IDs. It's all in the same table.

Maybe it isn't possible? If you would try again, I would be grateful.

 

[r937]

okay, i'm sorry, i wasn't making fun of you

i put "move" into double quotes to indicate that it would result in the effect that you wanted, but that actually, the rows aren't moved at all (instead, they would be updated in place)

consider that if you wanted to take the rows for ids 200,201 etc., and copy them to 800,801 etc., then the original rows would still be there, and the new rows would be copies

this is done with INSERT SELECT

but to move the rows, the new rows would end up in the table, but the original rows would be gone

this can be accomplished in two steps, INSERT SELECT followed by DELETE

but it can also be accomplished in one step -- UPDATE

the new rows are (stay) in the table, and the original rows are gone


r937.com | rudy.ca

 

[jude99]

Thanks very much for that. I follow much better. And thanks for the reassurance. Very good of you.

Seems to me now that the code suggested would, as it were, tack the information updated onto the end of the table.

Trouble is, I am hoping to put that data into empty columns in an equal number of rows which I would identify. I'm probably trying to be too specific - but unfortunately it doesn't help just to add them on.

 

[r937]

there is no "end" of a table -- the rows are stored anywhere at random, wherever there is space

you should not need to reserve rows to be filled in later

r937.com | rudy.ca

 

[jude99]

I think the penny is dropping. Thank you very much. You can't say where a group of changes must be inserted (updated into) only hope they will be inserted near the point you want.

 

[ingresman]

I think you've got the picture of a table wrong in your mind, I think you see it as a normal flat file. While a Mysql table is actualy physicaly a file that's just because it has to live somewhere. Some DB's like Oracle and Informix store many tables in huge file, again just for storage.
You say in your last post "only hope they will be inserted near the point you want". That's the thing, you don't know nor does it concern you where it goes, the DBMS does all that.
SQL works in sets of data which are usualy defined by a where clause.
Simple SQL looks really easy like select * from table where x="hello" etc and at this level will serve many developers well. I think you might need to get yourself a book to read through, I guarentee as you develop your app you will get further and further into difficultys.
Something like the awfully titled SQL for Dummies should get you on your way

 

[jude99]

Thanks for the explanation, and the recommendation,ingresman. Yes, I probably do have the picture wrong.

I will look out for that book - not my favourite collection, I must say - but of course I have many, but never find the precise wording of the query I'm after each time.

It's a problem with webwork, I suspect. You have your code and it works time after time and you seldom have to think about it. Suddenly you want to do something different, and you can't remember how to do a simple join.

Working even at the simplest level with mysql gives me an enormous kick. (I know it's magic; can't see why others pretend it's something technological.) I will have to go back to my primer tutorials, of course.

Many thanks for your input, ingresman.

 

[r937]

this might be considered inappropriate, but i thought i'd mention that i have a beginning SQL book coming out soon... google "Simply SQL"

r937.com | rudy.ca

 

[jude99]

Excellent,Rudy, and I don't think it inappropriate at all. After it sounds as though it will be of great value to us all. The link from Uncle G was

http://oreilly.com/catalog/9780980455250/

Odd it should be O'Reilly. I seem to recall quite a strong argument with him at Rhode Island on my trimaran after a singlehanded race across the pond.

I signed up for email notification, but I hope you will announce it on the forums, too. Selling tutorial books is really tough work - and you have helped an awful lot of us over the years. I've cuttings in my notebook from you from 2005, for instance. Well done!

 

[r937]

i'll probably mention it in my sig when the publisher (sitepoint) announces it

o'reilly and amazon have "pre"-announced it

thanks also for the kind words



r937.com | rudy.ca