1

I want a stored procedure to create a temptable based on 2 columns of table_A, and then check if there are rows in table_B (with 5 columns) that have these 2 columns like the way they are in table_A. Don't do anything to them a delete rows that are not duplicate.

Something like this:

Create Procedure DeleteExtra

as

Create Table #TempTotalHoney
(
HarvestDate Date,
HoneyType VarChar(50)
)

INSERT INTO #TempTotalHoney
Select HarvestDate, HoneyType
From tHoneyHarvest
Group BY HarvestDate, HoneyType

//until here temptable created as I want, but I don't know how to check
//not duplicated rows, I tried this But it is wrong...

Delete From tHoneyWeight
Where HarvestDate AND HoneyType Not in (select HarvestDate, HoneyType 
From #TempTotalHoney)

//must check these tow columns together not separately  


If(OBJECT_ID('tempdb..#TempTotalHoney') Is Not Null)
Begin
    Drop Table #TempTotalHoney
End

This is the error I get:

Msg 4145, Level 15, State 1, Procedure DeleteExtra, Line 17
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

Update:

this is #TempTotalHoney that created from table_A

HarvestDate           HoneyType
---------------------------------------------------
2017-01-10            Pure
2017-01-10            Semi-Pure
2017-02-03            Pure
2017-02-04            artificial

and

table_B:

RecID      HarvestDate    HoneyType   TotalCombs    TotalWeight
----------------------------------------------------------------
 1         2017-01-10     Pure            10           22
 3         2017-01-10     Semi-Pure       11           24
 4         2017-02-03     Pure            22           50
 6         2017-02-04     artificial      25           56
 8         2017-01-10     Semi-Art        10           18.5
 9         2017-02-05     Pure            11           19

I want the RecID 8 and 9 that combination of HarvestDate And HoneyType of them not exists in #TempTotalHoney be deleted.

SaraniO
  • 587
  • 6
  • 26

1 Answers1

2

You can try using below query

DELETE table_B
FROM table_B B
 LEFT JOIN #TempTotalHoney A 
  ON B.HarvestDate = A.HarvestDate 
   AND B.HoneyType = A.HoneyType
WHERE A.HoneyType is Null;

Hope this would help you out.

Viki888
  • 2,686
  • 2
  • 13
  • 16
  • tanks, I get this error: Cannot resolve the collation conflict between "Arabic_CI_AS" and "Arabic_100_CI_AI" in the equal to operation. – SaraniO Feb 01 '17 at 10:37
  • You seems to have a collation problem. Modify the `AND` condition like `AND B.HoneyType = A.HoneyType COLLATE Arabic_CI_AS` – Viki888 Feb 01 '17 at 10:43
  • yeeees, thanks a a lot, finally problem solved, I asked a question and because it remained unanswered I had to solve it with other solution and that caused me this problem that you just solved it. do you have time to take a look at that and see is there a better way? – SaraniO Feb 01 '17 at 10:51
  • Is this your question http://stackoverflow.com/questions/41934441/how-to-prevent-duplicate-update-in-sql-stored-procedure you are talking about? – Viki888 Feb 01 '17 at 10:56
  • ok intimate me when you are done. http://chat.stackoverflow.com/rooms/134577/discussion-between-viki888-and-saranio – Viki888 Feb 01 '17 at 11:08