CODEWITHSUNDEEP
mysqlsql
hellzone
hellzone

Reputation: 5236

Sql update statement with variable

I have two table named "user" and "logs".

user table has column named "userID" which is also pk.

logs table has two columns named "log_detail" and "userID".

What I want to query is "get all user.userID values one by one from user table and check them if log_detail value contains this value, If it countains then update logs.userID with this userID value".

I tried some queries but I really don't know how to do it.

By the way I am using Mysql.

UPDATE logs
SET logs.userID = user.userID
SELECT userID
FROM logs
WHERE logs.userID LIKE concat("%",user.userID,"%");

Upvotes: 0

Views: 89

Answers (4)

Maulik Shah
Maulik Shah

Reputation: 402

SQL Fiddle

Schema details

create table user
(userid varchar(30));

create table logs
(log_detail varchar(100),
 userid varchar(30));

insert into user values('user1');
insert into user values('user2');
insert into user values('user3');

insert into logs values('update by user1','user3');
insert into logs values('inserted by user2','user2');
insert into logs values('inserted by user3',null);

Table data before update

|        log_detail | userid |
|-------------------|--------|
|   update by user1 |  user3 |
| inserted by user2 |  user2 |
| inserted by user3 | (null) |

Update Query 

 update logs join user
set logs.userid=user.userid
where logs.log_detail LIKE concat("%",user.userID,"%");

Table data after update

|        log_detail | userid |
|-------------------|--------|
|   update by user1 |  user1 |
| inserted by user2 |  user2 |
| inserted by user3 |  user3 |

Upvotes: 1

Phani
Phani

Reputation: 93

Try with the following query.I think you will get what you need.

update logs l
  join users u
    on l.log_detail = u.userid
   set l.userid = u.userid

I think we no need to write where condition again,because we are using inner join in the query.

Thank you.

Upvotes: 0

Avrajit Roy
Avrajit Roy

Reputation: 3303

I am not very much familiar with MYSQL but in oracle we can satisfy your requirements like this

CREATE TABLE USERS
  ( USERID VARCHAR2(100 CHAR) PRIMARY KEY
  );

INSERT INTO USERS
  (SELECT 'AVROY'||LEVEL FROM DUAL CONNECT BY LEVEL < 10
  );

COMMIT;

CREATE TABLE logs_UID
  (
    log_dtl VARCHAR2(200 CHAR),
    UserID  VARCHAR2(100 CHAR)
  );

INSERT INTO logs_UID
SELECT 'test avroy'||level,NULL FROM dual CONNECT BY level < 10 ;

COMMIT;

MERGE INTO LOGS_UID lu USING USERS u 
ON (UPPER(u.userid) = UPPER(SUBSTR(LU.LOG_DTL,INSTR(UPPER(lu.LOG_DTL),UPPER(u.userid),1),LENGTH(lu.log_dtl)-INSTR(UPPER(lu.LOG_DTL),UPPER(u.userid),1)+1)))
WHEN MATCHED THEN
  UPDATE SET lu.USERID = u.USERID; 
COMMIT;

Upvotes: 0

apomene
apomene

Reputation: 14389

update logs
inner join 'user' on
    logs.userID = user.userID
set logs.userID = user.userID
WHERE logs.log_detail LIKE concat("%",user.userID,"%");

Also take into account that user is a reserved keyword in mysql

Upvotes: 0

Related Questions