copy (with update) certain content from the same table

sql copy records into same table change one field
sql copy data from one row to another in same table
sql copy rows into same table change one value
sql copy data from one column to another in same table
sql update row with data from another row in same table
select and insert into same table sql
postgresql copy row into same table
sql copy one row to another in same table

I wanted to UPDATE the value of my below table (row & col_md) :

Current Data

| id  | id_cat | row  | col_md |
| --- | ------ | ---- | ------ |
| 1   | 1      | 1    | 4      |
| 2   | 1      | 2    | 5      |
| 3   | 1      | 3    | 5      |
| 4   | 2      | 1    | 3      |
| 5   | 2      | 2    | 4      |
| 6   | 2      | 2    | 4      |
| 7   | 3      | 1    | 12     |
| 8   | 3      | 1    | 12     |
| 9   | 3      | 2    | 3      |

That may look something like the below table. (I want to have the same content of rows that id_cat=1 have, in rows with id_cat=2 & 3).

Required Data:

| id  | id_cat | row  | col_md |
| --- | ------ | ---- | ------ |
| 1   | 1      | 1    | 4      |
| 2   | 1      | 2    | 5      |
| 3   | 1      | 3    | 5      |
| 4   | 2      | 1    | 4      |
| 5   | 2      | 2    | 5      |
| 6   | 2      | 3    | 5      |
| 7   | 3      | 1    | 4      |
| 8   | 3      | 2    | 5      |
| 9   | 3      | 3    | 5      |

id_cat 2 and 3 should have the same "row" and "col_md" values as in id_cat=1.

I've tried with this post first answer like this:

UPDATE `myTable` AS t1 JOIN `myTable` AS t2 ON t2.id_cat=1
SET    t1.row = t2.row, t1.col_md = t2.col_md
WHERE  t1.id_cat = 2 or t1.id_cat=3;

but that results on all "row" column values equal to 1.

What I'm doing wrong and what's the way to do this right?

EDIT:

The tables above are just examples to make this ask easier to understand, but the real table is bigger (4k rows) and:

  • "row" column with id_cat=1 can have any number and not a sequence as in the example.
  • "col_md" columns can have any number too.
  • That's why the update must set a copy of the id_cat=1 "row" and "col_md" values in the id_cat!=1 "row" and "col_md" values.

If this can't be done with just MySQL, a php script will be nice too.

it's not enough to tell which group you want the data from, you need to match id to id.

in your case t2.id 4 and 7 to t1.id 1, t2.id 5 and 8 to t1.id 2, and t2.id 6 and 9 to t1.id 3.

SELECT @d := COUNT(*) FROM myTable WHERE id_cat = 1;

UPDATE `myTable` AS t1 
JOIN `myTable` AS t2 ON t2.id_cat=1 AND 
t2.id = IFNULL(NULLIF(t1.id MOD @d, 0), @d)
SET    t1.row = t2.row, t1.col_md = t2.col_md
WHERE  t1.id_cat = 2 or t1.id_cat=3;

@d holds the number of lines where id_cat = 1

we divide t1.id by @d and match the remainder (MOD) to t2.id.

when t1.id is multiple of @d the remainder is 0 and we have to match it to @d

so we make 0 into NULL and NULL into @d

How to copy data into same table based on existing data in sql , select * into #temptable from table where date='201839' update #temptable set date='201840' insert into table select * from #temptable update� First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.

In the example query you gave, you are updating t1.row with t2.row. As you are joining on the id_cat, this will result in multiple rows selected to update a single row, so the outcome just takes the first row.

What you actually want, is to make the 1-to-1 relation in the update, so what needs to be changed in your query is to add the row matching in the join and remove the assignment in the SET, like this:

UPDATE `myTable` AS t1 JOIN `myTable` AS t2 ON t2.id_cat=1 AND t1.row = t2.row
SET t1.col_md = t2.col_md
WHERE  t1.id_cat = 2 or t1.id_cat=3;

Which then gives the output of:

MariaDB [testart]> select * from myTable;
+------+--------+------+--------+
| id   | id_cat | row  | col_md |
+------+--------+------+--------+
|    1 |      1 |    1 |      4 |
|    2 |      1 |    2 |      5 |
|    3 |      1 |    3 |      5 |
|    4 |      2 |    1 |      4 |
|    5 |      2 |    2 |      5 |
|    6 |      2 |    3 |      5 |
|    7 |      3 |    1 |      4 |
|    8 |      3 |    1 |      4 |
|    9 |      3 |    2 |      5 |
+------+--------+------+--------+
9 rows in set (0.00 sec)

How to copy rows from the same table and update the ID column , Instead I INSERT INTO temp table and update the ID there. is to replicate existing accounts and some other account related tables, assigning them new account id. How to post data/code on a forum to get the best help. Instead of Updating table, alternate approach could be to Insert the required record into a new table. This can be achieved with following four steps. Create a tmp table with same fileds (id Auto_Increment, id_cat, row, col_md) Insert to tmp table with this statement

Currently able to achieve the SQL query for your desired result.

SELECT t2.id_cat, t1.row, t1.col_md 
FROM (SELECT row, col_md from mytable WHERE id_cat=1) as t1 , mytable as t2 
GROUP BY t2.id_cat, t1.row, t1.col_md

Copying Data From One Table To Another Using An INNER JOIN , UPDATE query in order to copy data from one database table to another. When this happens, some degree of data transformation and� Two tables in our database. Each record in the people table has an id and a name.Each record in the scores table has a personId which is linked people.id and a score.. If we wanted to retrieve data containing names next to scores, we could do this easily with a JOIN:

How to copy a row and insert in same table with a autoincrement , I have a table Eg- tab. What I am trying to do is copying a row with an autoincrement column ID=1 and insert the data into the same table with a� Step 3. Click on ‘Update Field' If you've made change to titles, or added or deleted sections, you'll see a couple options. Step 4. Click on ‘Update page numbers only' OR ‘Update entire table' Use ‘update page numbers only' if you want to keep the table exactly as-is and just update the numbering.

Managing tables | BigQuery, Update a table's: Expiration time; Description; Schema definition; Labels. Rename (copy) a table; Copy a table; Delete a table; Restore a deleted table. For more� In Object Explorer right-click the table you want to copy and click Design. Select the columns in the existing table and, from the Edit menu, click Copy. Switch back to the new table and select the first row. From the Edit menu, click Paste. From the File menu, click Savetable name. In the Choose Name dialog box, type a name for the new table

How to Copy the Records from One Column in a PostgreSQL Table , for a PostgreSQL table and copy data from another column into it. Fortunately, this task is easy to accomplish using the UPDATE command in SQL. by a table name, to have psql return a list of a specific table's column� I am trying to write a stored procedure to copy all rows within a table of a user where the LanguageVersionID is say 17, and replace the LanguageVersionID with a new value of say 25, but keeping all other values the same.

Different ways to update rows in a table using SQL progressing, If every field needs to be updated to the same value, you can do that using a simple UPDATE command. UPDATE table SET col = new_value;� No. However, there are ways to automate the creation of a list of columns for a table. One is even built into SSMS. Rt-click on a table, select <Script table as>, select <SELECT to> and choose an option. Then just copy the columns you need from the generated statement. If any new columns are added to the table in future

Comments
  • Let me understand this, you want id_cat 1 to have 1,2,3 rows then the next id_cat to have the same row value too? So, id_cat 2 will also have 1,2,3 rows and so on?
  • @tcadidot0 htat's right, english is not my native language, so if you can suggest an eddit, I'll take it.
  • how many distinct id_cat should you update? Only 2 (2 and 3) or are there more of them? Also, you say "copy (with update) and that is confusing: does your other id_cat rows exists (which would result in UPDATE only), or do you need to create them (INSERT)
  • @ThomasG not insert, just update.
  • @RobertoSepúlvedaBravo: which version of MySQL are you using?
  • @RobertoSepúlvedaBravo i modified my answer
  • An awesome solution! You've made me the day. Thanks too much.
  • @RobertoSepúlvedaBravo this code only updates the lines, doesn't add any. so, if the line count is not multiple of the number of id_cat = 1 lines, the series will be incomplete. (if you didn't have line 9 in the example, those values would be missing)
  • This is exactly what I was looking for, the original table was updated perfectly.
  • This UPDATE statement will result in all the 'row' with value 1 and all 'col_md' as value 4 (except 'id_cat'=1).
  • This looks fine to me.. did you actually test it @KamranFeroz?
  • I only wrote my own answer because the comments said this didn't work, and ended up almost duplicating this one.. but it includes an intermediate query so I will leave it for now