带变量的Sql更新语句 [英] Sql update statement with variable

问题描述

我有两个名为user"和logs"的表.

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

user 表有名为userID"的列，也是 pk.

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

logs 表有两列名为log_detail"和userID".

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

我要查询的是从用户表中一一获取所有 user.userID 值，并检查它们是否 log_detail 值包含此值，如果包含此值，则使用此 userID 值更新 logs.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.

顺便说一下，我使用的是 Mysql.

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,"%");

推荐答案

SQL Fiddle

架构详情

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);

更新前的表格数据

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

更新查询

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

更新后的表格数据

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

这篇关于带变量的Sql更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！