SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
-
Nov 24, 2009, 06:10 #1
- 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
-
Nov 24, 2009, 06:56 #2
- 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.
-
Nov 24, 2009, 10:31 #3
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.
-
Nov 24, 2009, 14:25 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Nov 29, 2009, 02:39 #5
- 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.
-
Nov 29, 2009, 06:32 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Nov 29, 2009, 10:53 #7
- Join Date
- Aug 2002
- Location
- Burnaby, BC
- Posts
- 84
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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';
Bookmarks