CODEWITHSUNDEEP
mysqlsql
jared_flack
jared_flack

Reputation: 1646

SQL query to INSERT, UPDATE, or DELETE

asset_property table: 

| asset_id | property_id | property_value |
|:--------:|:-----------:|:---------------|
| 146      | 1           | 4G             |
| 146      | 3           | 68723-239Gs    |
| 147      | 1           | A7             |

This is my query:

INSERT INTO asset_property (asset_id, property_id, property_value)
VALUES
  (146, 1, '4G'),
  (146, 2, 'Black & Decker'),
  (146, 3, ''),
ON DUPLICATE KEY
UPDATE property_value = VALUES(property_value);

Is there a way to say something like this:

INSERT INTO asset_property (asset_id, property_id, property_value)
VALUES
  (146, 1, '4G'),
  (146, 2, 'Black & Decker'),
  (146, 3, ''),
ON DUPLICATE KEY
UPDATE property_value = VALUES(property_value)
ON VALUES(property_value) IS NULL
DELETE FROM asset_property
WHERE asset_id = VALUES(asset_id)
AND property_id = VALUES(property_id);

Or I could use PHP to determine empty property_value sets and build a second query. So I would need this:

DELETE FROM asset_property
WHERE 'PRIMARY KEY' IN ('146-3');

The syntax is okay, but 0 rows are affected.

I've tried this:

SELECT 'PRIMARY KEY' FROM asset_property WHERE asset_id = 146;

But the result is:

| PRIMARY KEY |
|:------------|
| PRIMARY KEY |
| PRIMARY KEY |

The MySQL Manual seams to say you can't.

Upvotes: 1

Views: 4906

Answers (3)

Ian Clelland
Ian Clelland

Reputation: 44152

DELETE FROM asset_property
WHERE 'PRIMARY KEY' IN ('146-3');

The syntax is okay, but 0 rows are affected.

There aren't any syntax errors, but that doesn't mean it's okay :) @rodrigoap has already explained what that one means, and why 0 rows match.

If you need to delete several records matching a composite primary key, you're going to have to do one of these:

DELETE FROM asset_property
WHERE (asset_id = 146 AND property_id = 3)
   OR (asset_id = 146 AND property_id = 7)
   OR (asset_id = 146 AND property_id = 18)

or concatenate the fields and test that:

DELETE FROM asset_property
WHERE CONCAT(asset_id, '-', property_id) in ('146-3', '146-7', '146-18')

(this one will be slower, because it can't take advantage of any indexes)

or just execute multiple delete statements inside of a transaction:

BEGIN
DELETE FROM asset_property
WHERE asset_id = 146 AND property_id = 3
DELETE FROM asset_property
WHERE asset_id = 146 AND property_id = 7
DELETE FROM asset_property
WHERE asset_id = 146 AND property_id = 18
COMMIT

Upvotes: 1

Lamak
Lamak

Reputation: 70658

So, your PRIMARY KEY is composed from asset_id and property_id?. In that case you can do your DELETE like this:

DELETE FROM asset_property
WHERE asset_id = 146 AND property_id = 3

Upvotes: 0

rodrigoap
rodrigoap

Reputation: 7480

DELETE FROM asset_property
 WHERE 'PRIMARY KEY' IN ('146-3');

Deletes 0 rows because the where clause is false, the text 'PRIMARY KEY' is not in the list ('146-3'). 

SELECT 'PRIMARY KEY' FROM asset_property WHERE asset_id = 146;

This one is returning the text 'PRIMARY KEY' for each row in the table asset_property where asset_id is 146.

Upvotes: 0

Related Questions