If value exsists, update

Question

Ok, I have a table where I will collect users devices. When a user logs in, I want it to register the device, if it already exists, it would just want to update the timestamp. This is only when a user logs in.

The table looks like this:

device_id => BIGINT AUTOINCREMENT PRIMARY KEY
user_id => BIGINT FOREIGN KEY users(id)
device_name => VARCHAR(40) (Will be like 'Donald ducks iphone')
device_type => VARCHAR(10) (Will be defined by the client, like "IOS")
last_usage => TIMESTAMP (On update, new timestamp and all that)

So, when a user logs in, I have the user_id, device_name, device_type.

What I want: if the id, name and type already exists in a row, just update timestamp, else insert the values.

Since this has nothing to do with the primary key I dont know how to do this. Of course I could do something like first select these values, return that and do the update/insert afterwards, but this does not feel right :)

Answer 1

You can use REPLACE in MySQL 5.0+:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

Example:

REPLACE INTO TableName (user_id, device_id, device_name, device_type, last_usage) VAULES (1, 2, "Test Device Name, "Test Type", NOW()); 

Answer 2

First, make device_name and device_type unique.

Now you have a couple of options. You can use:

INSERT IGNORE INTO devices (...) VALUES(...)

or if you're worried important errors might get ignored, use:

INSERT INTO devices (...) VALUES(...)
ON DUPLICATE KEY UPDATE device_id = device_id

Note that this is different from REPLACE in that this doesn't affect the old records at all, whereas REPLACE will create a new ID and timestamp.

EDIT: Since you now want to update the timestamp, use:

INSERT INTO devices (...) VALUES(...)
ON DUPLICATE KEY UPDATE last_usage = NOW()

Answer 3

For Each r In Me.Lstbox_Tables.ItemsSelected

'Create a recordset with required data

sql = "SELECT * FROM " & Me.Lstbox_Tables.ItemData(r)

'tablename = "Gahpp00d_" + rs!Table_Name

Set rs = db.OpenRecordset(sql, dbReadOnly)

Dim SheetName As String

SheetName = Replace(Me.Lstbox_Tables.ItemData(r), "GAHPP00D_", "")


On Error Resume Next

excelWbk.Sheets(SheetName).Select

If Err.Number <> 0 Then

MsgBox Err.Number & ":" & Err.Description, vbCritical, "ERROR!!!"

MsgBox "Worksheet " & SheetName & " doesn't exist.", vbCritical, "Sheet Missing!!!"

excelWbk.Close False

appExcel.Quit


 Exit Sub

 End If

Answer 4

You may need to convert this to mysql as its mssql but something along the lines of:

declare @count int
declare @user varchar
declare @machine varchar
declare @type as varchar

select @count = (select count(*) from [table] where user = @user and machine=@machine and type=@type)

if @count = 0
begin
insert into
[table]
select
@machine,
@user,
@type,
current_timestamp
end

if @count > 0
begin
update
[table]
set timestamp = current_timestamp
where
machine = @machine
and
user = @user
and
type = @type
end

Regards,

Marcus