基于If语句的SQL查询重复记录

发布于 2024-08-30 20:56:55 字数 610 浏览 6 评论 0原文

我正在尝试编写一个 SQL 查询，该查询将根据另一个表中的字段重复记录。我正在运行 mySQL 5。

（我知道重复记录表明数据库结构很糟糕，但我没有设计数据库，也无法重做这一切 - 这是一个在 wordpress 上运行的 shopp 电子商务数据库。）

每个产品特定属性需要链接到相同的几个图像，因此产品将需要表中每个图像一行 - 数据库实际上并不包含图像，只包含其文件名。（图像是供客户选择的剪贴画）

基于这些记录...

SELECT * FROM `wp_shopp_spec` WHERE name='Can Be Personalised' and content='Yes'

我想做这样的事情..

对于与该查询匹配的每条记录，从 wp_shopp_asset 复制记录 5134 - 5139 但更改 id，使其成为unique 并将“parent”列中的单元格设置为具有表 wp_shopp_spec 中的“product”值。这意味着为与上述查询匹配的每条记录创建 6 个新记录，所有记录在“parent”中具有相同的值，但具有唯一的 id 和从原始记录复制的所有其他列（即记录 5134-5139）

希望这足够清楚 -非常感谢任何帮助。

原文

I'm trying to write an SQL query that will duplicate records depending on a field in another table. I am running mySQL 5.

(I know duplicating records shows that the database structure is bad, but I did not design the database and am not in a position to redo it all - it's a shopp ecommerce database running on wordpress.)

Each product with a particular attribute needs a link to the same few images, so the product will need a row per image in a table - the database doesn't actually contain the image, just its filename. (the images are of clipart for a customer to select from)

Based on these records...

SELECT * FROM `wp_shopp_spec` WHERE name='Can Be Personalised' and content='Yes'

I want to do something like this..

For each record that matches that query, copy records 5134 - 5139 from wp_shopp_asset but change the id so it's unique and set the cell in column 'parent' to have the value of 'product' from the table wp_shopp_spec. This will mean 6 new records are created for each record matching the above query, all with the same value in 'parent' but with unique ids and every other column copied from the original (ie. records 5134-5139)

Hope that's clear enough - any help greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

溺深海 2024-09-06 20:56:55

听起来您需要交叉连接到您想要的资产值。所以像这样：

Select spec.parent, asset.id as uniqueid, ...
From`wp_shopp_spec` spec
    Cross Join  (
                Select id
                From `wp_shopp_asset`
                Where Id Between 5134 And 5139
                ) asset
Where name='Can Be Personalised' 
    And content='Yes'

It sounds like you need to cross join to the asset values you want. So something like:

Select spec.parent, asset.id as uniqueid, ...
From`wp_shopp_spec` spec
    Cross Join  (
                Select id
                From `wp_shopp_asset`
                Where Id Between 5134 And 5139
                ) asset
Where name='Can Be Personalised' 
    And content='Yes'

回复收藏 0 原文

ぽ尐不点ル 2024-09-06 20:56:55

INSERT wp_shopp_asset
(parent, col1, col2, ...)
SELECT wp_shopp_spec.product, wp_shopp_asset.col1, wp_shopp_asset.col2, ...
FROM wp_shopp_spec
CROSS JOIN wp_shopp_asset
WHERE wp_shopp_spec.name='Can Be Personalised'
  AND wp_shopp_spec.content='Yes'
  AND wp_shopp_asset.id BETWEEN 5134 AND 5139

INSERT wp_shopp_asset
(parent, col1, col2, ...)
SELECT wp_shopp_spec.product, wp_shopp_asset.col1, wp_shopp_asset.col2, ...
FROM wp_shopp_spec
CROSS JOIN wp_shopp_asset
WHERE wp_shopp_spec.name='Can Be Personalised'
  AND wp_shopp_spec.content='Yes'
  AND wp_shopp_asset.id BETWEEN 5134 AND 5139

回复收藏 0 原文