1

I have a new data set that I have imported into a new table in my database. I need to copy a couple fields from the old table into the new table. There may be new records or dropped records in the new table so I only want to update if the name matches.

Here is what I have so far. This runs, but only the first record is updated:

update or ignore NEWDATA 
set CATEGORY=(select CATEGORY from OLDDATA), 
    KEY=(select KEY from OLDDATA) 
where Material=(select Material from OLDDATA);

I have tried it with and without the ignore statement.

Any help or suggestions would be greatly appreciated. Thanks in advance!!

wyoskibum
  • 1,869
  • 2
  • 23
  • 43

2 Answers2

1

What you need is the ability to join on an update statement - and unfortunately that is not available in sqlite. How do I make an UPDATE while joining tables on SQLite?

At first, I thought there isn't much you can do but then I played around with the replace statement I came up with this, which might work for you depending on your database schema:

replace into newdata
(material, category, key, extra_col1, extra_col2)
select o.material, o.category, o.key, n.extra_col1, n.extra_col2
from olddata o
inner join newdata n on o.material = n.material

There are a couple of catches with this approach. While the inner join makes it so it only replaces existing data (kind of like an update) a replace is a still a bit different then an update. In my test table I had to place primary key or unique key on the material column. Also, if you use foreign keys that reference this data you can run into issues if you have cascades and such. Since I can't see your schema I'm not sure if those will be problems for you or not.

Hope this helps.

Community
  • 1
  • 1
Tony Gibbs
  • 2,369
  • 1
  • 19
  • 18
  • Thanks for the reply! I'm getting the following error: table NEWDATA has 9 columns but 3 values were supplied. I only want to replace category and key columns. I may end up writting a routine to loop through the newdata and update the records. – wyoskibum Aug 21 '12 at 12:12
  • Just add the other columns you want to keep: select o.material, o.category, o.key, n.column1, n.column2, ... – Tony Gibbs Aug 21 '12 at 16:26
  • this didn't work for me just appended the join data to the bottom of the table... :/ – shigeta Nov 12 '19 at 17:43
-1

You may use update On duplicate or update on duplicate key statement. Here's the manual: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

seeker
  • 3,255
  • 7
  • 36
  • 68