CODEWITHSUNDEEP
sqloracle-database
user6832291
user6832291

Reputation: 1

INSERT INTO not working with my select

I need to copy some data from one table to another:

There is a table that has the correct date (AUDITLOG), column TIME but I need to put it in the USER table, column USERS_DATE and associate with the correct user...

The SELECT returns the correct data by itself, I'm having issue using the SELECT INTO statement. My hacked up code below:

INSERT INTO users (USERS_DATE) WHERE USERID=U.USERID
(SELECT U.USERID,
       javaTimeStampToDate (L.TIME)
          AS "Last Login"
  FROM COMPANY C,
       USERS U,
       AUDITMAP M,
       AUDITLOG L
 WHERE     C.COMPANYID = U.COMPANYID
       AND U.USERID = M.ROWID
       AND M.AUDITID = L.AUDITID
       AND C.APPLICATION = 'A'
       AND L.NOTES LIKE '%went inactive%'
       AND U.STATUS = 0);

Upvotes: 0

Views: 115

Answers (1)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below query for sql server. I think you need an update query. Also change your commas with JOIN condition.

  UPDATE U
    SET U.USERS_DATE=L.TIME
  FROM COMPANY C 
       JOIN USERS U ON C.COMPANYID = U.COMPANYID
       JOIN AUDITMAP M ON U.USERID = M.ROWID
       JOIN AUDITLOG L ON M.AUDITID = L.AUDITID
 WHERE   C.APPLICATION = 'A'
       AND L.NOTES LIKE '%went inactive%'
       AND U.STATUS = 0

use below query for oracle,

MERGE INTO USERS U
USING 
(
  SELECT M.ROWID,L.Time
  FROM AUDITMAP M 
       JOIN AUDITLOG L ON M.AUDITID = L.AUDITID
  WHERE L.NOTES LIKE '%went inactive%'
) Au ON (U.USERID = Au.ROWID) 
WHEN MATCHED THEN UPDATE 
    SET U.USERS_DATE = Au.TIME
WHERE U.STATUS = 0 AND  EXISTS (select 1
                            from COMPANY c
                            where .COMPANYID = U.COMPANYID AND C.APPLICATION = 'A')

Upvotes: 1

Related Questions