1

I'm having a issue where I am trying to create a cron job that copies from column 1 to column 2 in a table. I need to make it that if column 1 data is already in column 2 then do nothing, if not ADD to column 2.

Does anyone have any tips or suggestions on how to achieve this?

Mark
  • 11
  • 2

1 Answers1

0

When the data in column 1 is equal to the data in column 2, what is the harm in updating the row anyways?

UPDATE table SET column2 = column1

The data is always set in column 2 but when it was already the same data then column 1 nothing has changed

After reading your comment, I suggest the following:

Remove column 2 and create a new table called previous_usernames Add 3 columns to that table id, user_id and username

Update this table when the username is changed, not afterwards with a cron. That is unneccesary complicated.

first run this query INSERT INTO previous_usernames (user_id, username) (SELECT id, username FROM users WHERE id = ?)

Then run this query UPDATE users SET username = ? WHERE id = ?

When you want to make sure that the old username is stored correctly you can use transactions

After reading your new comments I suggest the following

You could create a UNIQUE index on the username and GUID column and try to insert every row. When the username and GUID combination exists, the query will fail and the record not be inserted.

How do I specify unique constraint for multiple columns in MySQL?

Community
  • 1
  • 1
AgeDeO
  • 3,104
  • 2
  • 20
  • 57
  • Sorry my original post wasn't clear. I'm trying to create a record to store previous usernames. This displays the column on a page that shows what names they've had in the past. I don't want to overwrite column 2 with new data but add usernames unless they already exist in column 2. – Mark Oct 09 '15 at 11:36
  • Thanks for the update, the only issue I have is I'm pretty limited to using cron scripts as I'm using a program that works with a gameserver that then updates the table with new players & new info. The issue though is the program has very limited functionality and isn't open sourced so without a cron job I can only rely on a php loading to do that command. A cron job would help as it could be done in the background but I have no idea how to just add data to a column instead of replacing it. Here is an idea of what I'm trying to achieve: https://gyazo.com/2e20c31e4fe829ff3e5355366c01defc – Mark Oct 09 '15 at 11:58
  • The problem is that mysql doesn't support lists in a single column very well. Can you create the new table, is that possible? If so, I will change the answer to be usable with crons – AgeDeO Oct 09 '15 at 12:00
  • Yes that could work, it is possible to create the data instead of update so it displays the table like this: https://gyazo.com/6eff0723cd381316c1b00667d237f0c3 That way if Name doesn't exist in table then add new record. That could work as users could search by the GUID and it would list names! – Mark Oct 09 '15 at 12:09
  • That is also a good way to go, do you still need help with this or do you know how to handle this? – AgeDeO Oct 09 '15 at 12:11
  • I have the idea in my head and it makes sense, but making it actually happen in a cron job is what is pestering me at the minute. How in a cron job would I do something like this: If name exists in table, do nothing, else copy ID, GUID & NAME into NEW record on the table. Sorry for the hassle I'm still a beginner really with php and mysql. – Mark Oct 09 '15 at 12:21