• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 60
  • Last Modified:

Insert row question into table

May I know whether ANY mysql code can just we insert the data like row 1 until row 19 i put date as '2017-11-01' and then on row 20 until row 40 i put date as '2017-11-02' etc .. something like this.Capture.PNG
0
Dave IT
Asked:
Dave IT
  • 2
1 Solution
 
Ryan ChongCommented:
this is not possible for distinguish between the records.... as there is no unique identifier if you just having columns: Power State and date while all your records contain same values. when you update the records with values, you will all get the same values.

you need at least one unique identifier in this case.
1
 
Dave ITnewbie ITAuthor Commented:
If i add 1 more column put as Name .. so i can put the value like that?
0
 
Ryan ChongCommented:
it's fine to have a unique field, so that we can update the values accordingly, but it would be good if you can tell us the "sequence" as well.

if the table is sorted alphabetically by a field (let's called it sortcol), then you can try:

DROP TEMPORARY TABLE IF EXISTS tmp;

CREATE TEMPORARY TABLE tmp AS (
SELECT 
    (@row_number:=@row_number + 1) idx, `Name`, `date`, sortcol
FROM
    yourtable,(SELECT @row_number:=0) AS t
ORDER by sortcol  
);

Update tmp set `date` = '2017-11-01' where idx <= 19;
Update tmp set `date` = '2017-11-02' where idx >= 20 and idx <= 40;

update yourtable a inner join tmp b
on a.`Name` = b.`Name`
set a.`date` = b.`date`;

select * from yourtable order by sortcol;

Open in new window

1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now