带变量的Sql更新语句 [英] Sql update statement with variable
本文介绍了带变量的Sql更新语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个名为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,"%");
推荐答案
架构详情
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屋!
查看全文