6

How would I copy entire rows within a table and change one value?

 insert into user (
     user_id,account_id,user_type_cd,name,e_mail_addr,
     login_failure_cnt,admin_user,primary_user
 )
 select * from pnet_user where account_id='1'

But now I want to change 1 to 2 on the inserted entries. But now I want to change 1 to 3 on the inserted entries. But now I want to change 1 to .... on the inserted entries. But now I want to change 1 to 1000 on the inserted entries.

It will copy and write down 1000 times (only changing id ).

Yuck
  • 49,664
  • 13
  • 105
  • 135
Ch L
  • 101
  • 1
  • 2
  • 6
  • This is a SQL and/or PHP question. That isn't even a WordPress table you are dealing with. Perhaps ask to have it moved to [so] – s_ha_dum May 21 '13 at 12:45
  • actually i don't understand which value do you want to change but, if you mean `account_id` on where clause you can use `BETWEEN 1 AND 1000` statement – rcpayan May 21 '13 at 20:50
  • Which column are you wanting to change? Stop using * and replace with the values for each column you want. – Adam Wenger May 21 '13 at 20:50

1 Answers1

21

I'm not completely sure I understand what you're asking. If you want to copy the records where Account_ID = 1 into new rows and change Account_ID to 2 (or whatever number), this should work for you:

insert into user (user_id,account_id,user_type_cd,name,e_mail_addr,
     login_failure_cnt,admin_user,primary_user)
select user_id,2,user_type_cd,name,e_mail_addr,
     login_failure_cnt,admin_user,primary_user 
from pnet_user where account_id='1'

Basically, replace Account_ID with the value 2. If Account_ID is a varchar, use single quotes around it instead.

sgeddes
  • 62,311
  • 6
  • 61
  • 83