Delete rows in table, based on multiple criteria

Question

0.00/5 (No votes)

See more:

I have a table named poa and 8 fields, and 2 of these fields are year2 and proyecto.
I want to delete data based on those 2 fields, so that when I select a project in my website and then a year, let's say 2017, the rows that match these 2 filds will get deleted.
Can someone please help me?
Thanks.

What I have tried:

delete from poa where year2 like '2017' and proyecto like 'proy1';

Posted 7-Dec-17 7:10am

Member 10850253

Updated 7-Dec-17 8:09am

Add a Solution

Comments

[no name] 7-Dec-17 13:57pm

And what is the Problem?
What I see is you are using LIKE without a Joker character.

Finally your SQL is equal to delete from poa where year2 = '2017' and proyecto = 'proy1';

Most probably that is your Problem.

Member 10850253 7-Dec-17 14:12pm

I also tried that, and it didn't work. If you have any other suggestions, please let me know.
Thanks.

[no name] 7-Dec-17 14:16pm

What you tried exactly?
Something like delete from poa where year2 = '2017' and proyecto = '*proy1*';

Anyway, describe more exactly what your are doing, what you expect and what you get.

[Edit]
BTW it Looks like mySQL's Joker character seems to be '%' and _not_ '*'

Member 10850253 7-Dec-17 14:35pm

Exactly that, but without the joker character.

[no name] 7-Dec-17 14:36pm

Does a record exists which mach your criterions?

Member 10850253 7-Dec-17 14:45pm

Yes. I have different years and the same project, but all the table gets deleted.

[no name] 7-Dec-17 14:51pm

Give an example of live data which you expect to delete. Use "Improve question" for this, that everybody gets this Information.

[no name] 7-Dec-17 14:54pm

Different year2 and others than 2017 will be deleted?

Member 10850253 7-Dec-17 14:35pm

Exactly that, but without the joker character.

Member 10850253 7-Dec-17 14:54pm

I think the problem may be that proy1 is the same in poth cases, but the thing that changes is year2. I tried all these suggestions and all the table gets deleted. Please let me know how I can fix this.
Thanks.

Member 10850253 7-Dec-17 15:03pm

you were right, I was looking at another piece of code. I worked, thanks.

[no name] 7-Dec-17 15:06pm

BTW: This Looks fine for me (sqlfiddle does not allow delete, but I expect, that delete should be same):
SQL Fiddle[^]

Member 10850253 7-Dec-17 14:32pm

I tried that already and had the same result.
Please help me find another solution.
Thanks.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-12-07T08:07:00

LIKE is not a good choice here: it's generally used for strings, and a YEAR column should be an integer, not a string.
And LIKE is only really useful when you give it a wildcard specification, similar to "*.jpg" in Windows will return all JPEG files in a folder.
For MySQL and SQL Server, the primary wildcard is the '%' character which matches anything in the same ways the '*' dowes in Windows.

If you don't need wildcards, don't use LIKE; use '=' instead:

SQL

DELETE FROM poa WHERE year2 = 2017 AND proyecto = 'proy1';

Delete rows in table, based on multiple criteria

1 solution

Solution 1

Add your solution here

Preview 0

Delete rows in table, based on multiple criteria

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us