hungryhorse hungryhorse - 6 days ago 6
SQL Question

Adding new rows with existing data using SQL

I have the following table:

Table1

And all I want to do is add two new rows per PersonID i.e.

Table 2

These are just small snip bits from the actual table, as this table contains large amount of data.

Any help would be greatly appreciated.

Answer

Use INSERT?

INSERT INTO yourTable (ID, PersonID, JobID, Verified)
SELECT NULL, PersonID, MAX(JobID) + 1, 0
FROM yourTable
GROUP BY PersonID
UNION ALL
SELECT NULL, PersonID, MAX(JobID) + 2, 0
FROM yourTable
GROUP BY PersonID

Explanation:

Inserting NULL for the ID column should force the database to assign the next sequence, assuming that ID be an autoincrement column. If it is not, then maybe consider changing this. For each of the new records, the JobID sequence is just continued, hence my use of n+1 and n+2. And you seem to be using zero as the default value of Verified, so this was hard coded.

Comments