I really don't have a "test" table to try this with, and I thought
that it might be better to just ask, anyway, since there might be a
better option.

I have 2 tables with the same columns. I want to move a row from Table
A to Table B:

INSERT INTO table_b SELECT * FROM table_a WHERE id=$id

However, it's likely that $id already exists in Table B, so I need it
to FIND the row by the ID column, but then at the same time change it
to something that I specify when it writes it to Table A.

So, assuming that $id=477, but in the new table I want it to be 17238.
Is this the correct command?

INSERT INTO table_b SELECT * FROM table_a WHERE id=477 SET id=17238

Either way, is there a better/more efficient solution than this?

TIA,

Jason

On Mon, 06 Aug 2007 02:05:08 +0200, Jason <jwcarlton@gmail.com> wrote:

> I really don't have a "test" table to try this with,

Well, make one

> and I thought
> that it might be better to just ask, anyway, since there might be a
> better option.
>
> I have 2 tables with the same columns. I want to move a row from Table
> A to Table B:
>
> INSERT INTO table_b SELECT * FROM table_a WHERE id=$id
>
> However, it's likely that $id already exists in Table B, so I need it
> to FIND the row by the ID column, but then at the same time change it
> to something that I specify when it writes it to Table A.
>
> So, assuming that $id=477, but in the new table I want it to be 17238.

What is the logic behing 17238?

> Is this the correct command?
>
> INSERT INTO table_b SELECT * FROM table_a WHERE id=477 SET id=17238
>
> Either way, is there a better/more efficient solution than this?

You might use a LEFT JOIN to table_b in the SELECT statement, and set the
id according to wether it has a match in table_b

Then again: what is the point of id's if they change? If it's for
backwards compatibility, I'd just make a table_c with an real new id
field, and some old_id, and fill it with table_a & table_b both....
--
Rik Wasmus

Jason wrote:
> I really don't have a "test" table to try this with, and I thought
> that it might be better to just ask, anyway, since there might be a
> better option.
>
> I have 2 tables with the same columns. I want to move a row from Table
> A to Table B:
>
> INSERT INTO table_b SELECT * FROM table_a WHERE id=$id
>
> However, it's likely that $id already exists in Table B, so I need it
> to FIND the row by the ID column, but then at the same time change it
> to something that I specify when it writes it to Table A.
>
> So, assuming that $id=477, but in the new table I want it to be 17238.
> Is this the correct command?
>
> INSERT INTO table_b SELECT * FROM table_a WHERE id=477 SET id=17238
>
> Either way, is there a better/more efficient solution than this?
>
> TIA,
>
> Jason
>

INSERT INTO table_b (id, cola, colb, colc, cold) SELECT 17238m cola,
colb, colc, cold FROM table_a where id=477

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> What is the logic behing 17238?
<snip>
> Then again: what is the point of id's if they change? If it's for
> backwards compatibility, I'd just make a table_c with an real new id
> field, and some old_id, and fill it with table_a & table_b both....

I had an old format using flat text files, and it worked well at the
time by having the text files in different directories. I wrote that
program about 7 years ago, though, when I had about 20 users a week.
Now, I have closer to 50,000 users a week, which is why I've moved it
to MySQL, and at the time didn't take this in to consideration.

Now, I'm sort of stuck using separate tables for different boards, so
that old links will continue to work.

Thanks for the tip,

Jason

> INSERT INTO table_b (id, cola, colb, colc, cold) SELECT 17238m cola,
> colb, colc, cold FROM table_a where id=477

Just so that I can understand this...

When you wrote SELECT 17238m, was the "m" a typo, or does it have a
special meaning that I'm not aware of?

TIA,

Jason

Jason wrote:
>> INSERT INTO table_b (id, cola, colb, colc, cold) SELECT 17238m cola,
>> colb, colc, cold FROM table_a where id=477
>
> Just so that I can understand this...
>
> When you wrote SELECT 17238m, was the "m" a typo, or does it have a
> special meaning that I'm not aware of?
>
> TIA,
>
> Jason
>

A typo - should be a comma (17238,)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================