SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Location
    Burnaby, BC
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HELP: HOW TO - Duplicate ROW content to new ROW

    I am wondering if there is a way to DUPLICATE (MOST) of the contents in a mysql (phpmyadmin) ROW into a new row? Obviously not duplicate the primary since that needs to be unique.

    I just need to know if there is a SQL to duplicate the fields and contents from one row to a new row. I can easily specifiy the FIELD's if needed (be better if I can specify like a " * " but a phrase to exclude some fields. BUT I CANNOT specify the CONTENTS of the fields. That would defeat the purpose. Basically a SQL Query in simple terms:

    SELECT * from [TABLE] EXCEPT [FIELDS, FIELDS, FIELDS, FIELDS] where [MID (primary field) = "### into a NEWLY CREATED ROW with the contents into this new ROW.

    Thanks,

    Paul

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    INSERT INTO table_name ( field1, field2, field3 )
    SELECT field1, field2, field3 FROM table_name WHERE key_field = 'some_value';

    There's no way of specifying a * minus certain columns I'm afraid.

  3. #3
    SitePoint Enthusiast freezea's Avatar
    Join Date
    Apr 2009
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    insert into t(c1,c2,c3) select c1,c2,'z' from t where pk = 4711

    As seen, you can mix columns and constants in the select list. Also, assuming that the primary key column is defined as autoincrement.
    RAQ Report: Web-based Excel-like Java reporting tool.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    freezea, if you're going to steal swampboogie's code, please at least give him proper credit, or rewrite the example in your own words

    you even copied his comment word for word

    plagiarism earns you no respect whatsoever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Location
    Burnaby, BC
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    INSERT INTO camaro_models (1305, engine_id, trans_id, std_engine, opt_engines, year, car_type, trim_type, man_plant, front_susp,
    rear_susp, steering, front_tires, rear_tires, front_brake_type, rear_brake_type, wheelbase, length, width, height, front_tread, rear_tread,
    ground_clearance, front_headroom, rear_headroom, front_legroom, rear_legroom, front_shoulderroom, rear_shoulderroom, front_hiproom,
    rear_hiproom, std_transmission, opt_transmission, exhaust_system, rear_axle_ratio)

    SELECT base_model, engine_id, trans_id, std_engine, opt_engines, year, car_type, trim_type, man_plant, front_susp, rear_susp, steering, front_tires, rear_tires, front_brake_type, rear_brake_type, wheelbase, length, width, height, front_tread, rear_tread, ground_clearance,
    front_headroom, rear_headroom, front_legroom, rear_legroom, front_shoulderroom, rear_shoulderroom, front_hiproom, rear_hiproom,
    std_transmission, opt_transmission, exhaust_system, rear_axle_ratio FROM camaro_models WHERE mid = '1292';


    Any idea why I am getting a

    MySQL said:
    #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 '1305, engine_id, trans_id, std_engine, opt_engines, year, car_t


    Basically ROW 1305 (variable "MID") already exists with temp information. I am trying to copy ROW 1292 (Variable "MID") and overwrite MID 1305 with the information in 1292.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the reason you're getting the syntax error is because 1305 isn't a column name

    if you want to "overwrite" a row, what you want to do is delete it first, before inserting it again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Location
    Burnaby, BC
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the reason you're getting the syntax error is because 1305 isn't a column name

    if you want to "overwrite" a row, what you want to do is delete it first, before inserting it again
    I'm confused then..

    Code:
    UPDATE camaro_models (engine_id, trans_id, std_engine, opt_engines, year, car_type, trim_type, man_plant, front_susp,
     rear_susp, steering, front_tires, rear_tires, front_brake_type, rear_brake_type, wheelbase, length, width, height, front_tread, rear_tread,
    ground_clearance, front_headroom, rear_headroom, front_legroom, rear_legroom, front_shoulderroom, rear_shoulderroom, front_hiproom, 
    rear_hiproom, std_transmission, opt_transmission, exhaust_system, rear_axle_ratio) WHERE mid = '1305'
    
    SELECT mid, engine_id, trans_id, std_engine, opt_engines, year, car_type, trim_type, man_plant, front_susp, rear_susp, steering, front_tires, rear_tires, front_brake_type, rear_brake_type, wheelbase, length, width, height, front_tread, rear_tread, ground_clearance, 
    front_headroom, rear_headroom, front_legroom, rear_legroom, front_shoulderroom, rear_shoulderroom, front_hiproom, rear_hiproom, 
    std_transmission, opt_transmission, exhaust_system, rear_axle_ratio FROM camaro_models WHERE mid = '1292';
    Tried this too.. Basically I want to update part of the information from row MID 1292 into row MID 1305..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •