CODEWITHSUNDEEP
mysqlsql-serversql-update
grhmstwrt
grhmstwrt

Reputation: 341

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      | [email protected]
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?

Upvotes: 2

Views: 2285

Answers (2)

Tyler Roper
Tyler Roper

Reputation: 21672

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]

Upvotes: 2

ash
ash

Reputation: 1232

The quickest way I can think of doing this is creating a temporary table with the new data that you want to add (you could possibly bulk import it all from say, a CSV file).

The temporary table will just need a couple of columns - one with user_id and the other one dob - you'll be getting rid of it after anyway.

You could then do something like this:

UPDATE tbl_user_variables_dobRE a
  JOIN tmp_table b
    ON ( a.user_id = b.user_id )
   SET a.dob = b.dob

Once you've done that you can DROP your temporary table and be good to go - good luck!

Important

Be super-careful when updating data - it's so easy to mess up your data by forgetting to add a clause. If possible, do this with some test data before trying it with the real production data.

Upvotes: 2

Related Questions