CODEWITHSUNDEEP
sqlsql-server
ocean800
ocean800

Reputation: 3737

SQL - Compare values from two different tables?

So I have two different tables, update_metadata with one row that looks like:

|Updated_Date|
2020-06-05 00:00:00.0000000

and another table where I am querying a single max date from:

select max(distinct Date_Col) from data_table

Basically, I want to run a procedure Update_New_Data if Updated_Date < Date_Col, i.e if the data_table has been updated. But I'm unsure how exactly to set up the query. I tried:

WITH data_date AS (select max(distinct Date_Col) FROM data_table),
update_date AS (select Updated_Date from update_metadata)
IF ( update_date < data_date ) 
BEGIN
**procedure SQL query here**
END

But I tried it and it looks like I can't use WITH with IF this way, from this error:

Incorrect syntax near the keyword 'IF'.

How can this be done properly?

Upvotes: 0

Views: 54

Answers (1)

Dale K
Dale K

Reputation: 27451

There is no need for a CTE - just move your queries directly into the IF statement as follows:

IF (SELECT Updated_Date FROM update_metadata) < (SELECT MAX(Date_Col) FROM data_table) 
BEGIN
  EXEC Update_New_Data;
END;

NB: Best practice is to fully qualify all table names and stored procedure names with the schema e.g. dbo.Update_New_Data

You don't need DISTINCT either.

Upvotes: 2

Related Questions