Need help in simple sql to add record

Question

We use (INSERT INTO) to insert a record in the table which creates more than one record when used again. Is there any way to add a record and alternately replacing the prevoius one without adding any new record. I know this would work:

UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';

But what if there is no condition ie. we don't know the record, we only know the column name. Is there any way to fill only one record and alternately replace the previous record without creating 2nd record?

Any UPDATE without a condition will update all rows in the database. You could write a procedure. — Jay Blanchard
– Jay Blanchard, Commented May 12, 2015 at 20:17
if you don't have a condition, how do you know what the previous record is? — Jonathan Kuhn
– Jonathan Kuhn, Commented May 12, 2015 at 20:17
@karan, without a condition, how would you know which row to remove/replace? — Jonathan M
– Jonathan M, Commented May 12, 2015 at 20:20
or INSERT ... ON DUPLICATE KEY UPDATE dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html — Funk Forty Niner
– Funk Forty Niner, Commented May 12, 2015 at 20:21

Dean · Accepted Answer · 2015-05-12 21:10:02Z

1

OK... updating if a record exists or creating a record if there are zero records is a pretty simple matter and you have a solution for it. That having been said, I would do something different and keep track of my message of the day by date:

-- This is REALLY BASIC, but, just to give you the idea...
CREATE TABLE [dbo].[MessageOfTheDay](
    [MessageDate] [date] not null,
    [MessageContents] [nvarchar](500) not null,
    UNIQUE (MessageDate)
)

declare @MessageContents nvarchar(500), @MessageDate date
set @MessageContents = 'This is the new MOTD!!!'
set @MessageDate = GETDATE()

-- Every day, create a new record and you can keep track of previous MOTD entries...
insert into MessageOfTheDay(MessageDate, MessageContents)
values (@MessageDate, @MessageContents)

-- Get the message for today
select MessageContents from MessageOfTheDay where MessageDate = @MessageDate

-- If you want, you can now create messages for FUTURE days as well:
set @MessageContents = 'This is tomorrow''s MOTD!!!';
set @MessageDate = dateadd(D, 1,GETDATE())

insert into MessageOfTheDay(MessageDate, MessageContents)
values (@MessageDate, @MessageContents)

-- Get tomorrow's message
select MessageContents from MessageOfTheDay where MessageDate = @MessageDate

-- If you aren't necessarily going to have one per day and want to always just show the most recent entry
select MessageContents from MessageOfTheDay order by MessageDate desc limit 1

Anyway, that's just my $.02. At some point I bet you will want to look over the history of your MOTD and when you do, you will be happy that you have that history. Plus, this more accurately models the data you are trying to represent.

answered May 12, 2015 at 21:10

Dean

2,32122 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

karan dahiya Over a year ago

Can you come on team viewer for a minute? @Dean

Dean Over a year ago

Is one of those queries giving you problems?

karan dahiya Over a year ago

Yes, I'm unable to understand. Are you on facebook or twitter or something like that where I can contact you?

karan dahiya · Accepted Answer · 2015-05-12 20:43:32Z

0

I got my answer and It's working now! I used:

INSERT INTO data (a, b, c)
VALUES
('1','2','3')
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b)

answered May 12, 2015 at 20:43

karan dahiya

11 bronze badge

Collectives™ on Stack Overflow

Need help in simple sql to add record

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related