Get the maximum value from row and insert in one sql query-CodePudding

I have got following columns in my table feedTable

FirstName
LastName
FeedDate

I have got below rows in my table

XYZ, XYZ, '2021-10-15'
ABC, ABC, '2021-10-16'
ANA, ANA, '2021-10-15'
ALO, ALO, '2021-10-16'
PAIO, PAIO, '2021-10-16'

Now I want to insert a record with first name, lastname and maximum date from existing records. It should be in same query. I don't want to use two operation where I need to get maximum date and then insert. What could be the best way to perform this operation?

Insert into feedtable (FirstName, LastName, FeedDate) values ('Rahul','B',?)

Here In place of '?' I wanted to place maximum date from feeddate. How is it possible?

Please help

CodePudding user response：

  CREATE TABLE feedTable (
    FirstName varchar(50),
    LastName varchar(50),
    FeedDate  date);

--Date format MM/dd/yyyy
INSERT INTO feedTable VALUES( 'Shohag', 'Mia', '3/31/2021')
INSERT INTO feedTable VALUES( 'Raju', 'Ahmed', '4/6/2021')
INSERT INTO feedTable VALUES( 'Robin', 'Mia', '4/10/2021')
INSERT INTO feedTable VALUES( 'Emon', 'Paki', '12/8/2021')
INSERT INTO feedTable VALUES( 'Saiful', 'Haque', '3/1/2021')

--for checking current max value
SELECT * FROM feedTable ORDER BY FeedDate

--Inserting into main table after selecting max data
INSERT INTO  feedTable 
SELECT FirstName, LastName,FeedDate
FROM feedTable
Where FeedDate =  (SELECT MAX(FeedDate) FROM feedTable)

--for checking value after inserting
SELECT * FROM feedTable ORDER BY FeedDate

Or possible second option could be according to your requirement:

Insert into feedtable (FirstName, LastName, FeedDate) values ('Rahul','B', (select MAX(FeedDate) from feedTable) )

CodePudding user response：

Please try this where first name and last name is input and date is max date from existing table record.

INSERT INTO feedTable (FirstName, LastName, FeedDate)
SELECT 'Rahul' FirstName
     , 'B' LastName
     , MAX(FeedDate) FeedDate
FROM feedTable

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1a3ffbc0c701843f7eabf23e40c1d49c