gsusonline gsusonline - 2 months ago 7
MySQL Question

insert/update mysql column based on existing data

I'm updating an existing table by adding data into an existing column.

I have already have an output of the data to be inserted, but due to the amount of records, i'm looking for the best way to insert this into my table without having to manually write to each line of sql.

Here's my sql (partial) i want to insert into

INSERT INTO `tbl_user_variables_dobRE` (`user_id`, `value`) VALUES
(150, '1959-11-02'),
(151, '1948-04-20'),
(152, '1961-06-18'),


And this is the table i want to insert it into

id | 7
username | guestinvite
password | BLANK
forname | forname
surname | surname
email | guestinvite@test.com
address_id | 286
type_id | 4
dob | 0000-00-00
plusGuest | 0
update | 2016-02-16 11:54:36
created | 2016-04-04 17:03:12


So i want to insert the second item into the 'dob' column where first item = id

Is there anyway to do this programmatically or do i have to write WHERE & OR statements for every line?

Answer

You tagged both MySql AND sql-server in your post. The following is assuming you're using SQL Server, but the idea would remain the same in MySQL (just different syntax)...

If I'm understanding correctly, it sounds like you want to do an UPDATE, not an INSERT, being that you're modifying existing rows.

You said that you have an output of the data to be inserted - Insert this into a TEMP table and JOIN it to the table you'd like to update where the id's match.

BEGIN TRANSACTION [Transaction1]  -- Do large updates as transactions to avoid data loss

CREATE TABLE #temp ( -- Create temp table
    [user_id] int,
    [dob] nvarchar(20)
)

INSERT INTO #temp
   -- YOUR SELECT GOES HERE
   SELECT my_id as [user_id], my_dob as [dob]


UPDATE my_table
SET my_table.dob = t.dob
FROM tbl_user_variables_dobRE my_table
INNER JOIN #temp t ON t.user_id = my_table.id

DROP TABLE #temp

If your data looks good, commit the transaction: (Don't dwell too long, transactions lock table data!)

COMMIT TRANSACTION [Transaction1]

Otherwise:

ROLLBACK TRANSACTION [Transaction1]
Comments